Sorts delete rows
Hey guys,
I'm ussing SSIS 2008 and when I try to sort a dataset so I can be able to do a merge join the sort task delete rows from my dataset. For example before the Sort task I've 100 rows and after 23. I have the property "delete duplicate rows" unchecked so I don't
know what can be the problem.
Any ideas?
Regards,
Rodrigo.
June 27th, 2011 9:23pm
The Sort transformation can also remove duplicate rows as part of its sort. Duplicate rows are rows with the same sort key values. The sort key value is generated based on the string comparison
options being used, which means that different literal strings may have the same sort key values. The transformation identifies rows in the input columns that have different values but the same sort key as duplicates. Please change the sort key to keep duplicateshttp://uk.linkedin.com/in/ramjaddu
Free Windows Admin Tool Kit Click here and download it now
June 27th, 2011 9:37pm
Thanks for answering RamJaddu.
Where do I change the sort key?
June 27th, 2011 9:45pm
Sort Transformation Editor ->Available Input Columns ->Using the check boxes,
specify the columns to sort.http://uk.linkedin.com/in/ramjaddu
Free Windows Admin Tool Kit Click here and download it now
June 27th, 2011 9:51pm
I specified the columns to sort as you said and it word just fine. But problem is, I'm "losing" the columns that were not checked in availiable input columns. How can I do to bring all the columns checking only two of them?
June 27th, 2011 10:03pm
I think there is an easer workaround that even does not involve the Sort Component. Just use the Sort key position property on the OELDB source as seen here
http://tinyurl.com/42mx2le
or you can use this post on how to avoid the sorting transformation for more info if needed: http://www.ssistalk.com/2009/09/17/ssis-avoiding-the-sort-components/
Arthur My Blog
Free Windows Admin Tool Kit Click here and download it now
June 27th, 2011 10:07pm
Why don't you check more columns than use what ever columns you need .....http://uk.linkedin.com/in/ramjaddu
June 27th, 2011 10:08pm
Because if I checked all the columns (I need all of them) the sort task brings me only 23 rows! Instead of bringing 100 rows...
Free Windows Admin Tool Kit Click here and download it now
June 27th, 2011 10:21pm
I think there is an easer workaround that even does not involve the Sort Component. Just use the Sort key position property on the OELDB source as seen here
http://tinyurl.com/42mx2le
or you can use this post on how to avoid the sorting transformation for more info if needed: http://www.ssistalk.com/2009/09/17/ssis-avoiding-the-sort-components/
Arthur My Blog
See the above if works for youArthur My Blog
June 27th, 2011 10:52pm
You're saying that 100 rows enter the Sort component (not Task), you have the "remove rows" option UNchecked, and only 23 rows exit the component?
One question - how do you know 23 rows exit the Sort component - where are you seeing this, and at what point are you seeing this?
Talk to me now on
Free Windows Admin Tool Kit Click here and download it now
June 28th, 2011 3:34am
Here is the image: http://i56.tinypic.com/35dbux1.jpg
As you can see problem is in "sort 4"
July 2nd, 2011 8:08am
Yes, that was the first thing I tried...
Free Windows Admin Tool Kit Click here and download it now
July 2nd, 2011 8:55am
That's really weird. Is there any way you could capture some of the data going into the sort - say 100 rows - that exhibits the same behaviour, and package that up so I could try it? (Use a multicast, a sampling transform, and a Raw Dest?)
As far as I've ever seen, the Sort never loses rows unless the remove duplicates options is used. It is an asynchronous component, so the number of rows out isn't guaranteed to be the same as the number of rows in. It's up to the component implementation
to ensure that - but I've never seen this problem before. I've seen tons of "user" problems before with misconfigured Sorts - but those usually result in "odd" row counts after a Merge Join (because the rows aren't matching like the user thinks they
should).
Talk to me now on
July 2nd, 2011 11:18am
This one is the editor:http://i55.tinypic.com/2mrwhow.jpg
Advanced editor (input columns):
http://i51.tinypic.com/2vmeu4o.jpg
Free Windows Admin Tool Kit Click here and download it now
July 2nd, 2011 11:24am
See what, exactly? Can you describe the components (what kind), how they're attached to each other, and where you see the row counts?
Talk to me now on
July 2nd, 2011 12:01pm
Have you tried removing this Sort and adding a new Sort component?
Talk to me now on
Free Windows Admin Tool Kit Click here and download it now
July 2nd, 2011 5:45pm
In order to attach an image, you have to upload it to someplace like SkyDrive, Picasa, ImgShack, etc..., then get the URL of the image, and input an IMG tag. There are instructions in the sticky at the top of the forum.
Talk to me now on
July 2nd, 2011 8:01pm