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

This topic is archived. No further replies will be accepted.

Other recent topics Other recent topics