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.

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

Thanks for answering RamJaddu. Where do I change the sort key?

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?

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

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...

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

But I need to sort a new derived column so it can be joined with another dataset. Anyway thanks for those links, but in this case won't be helpful because that's for an oledb source..

Here is the image: http://i56.tinypic.com/35dbux1.jpg As you can see problem is in "sort 4"

While on debug mode I see it in the data flow task above the connectors.

Yes, that was the first thing I tried...

Look into your data all - Merge Join 1 loses data, Merge Join does not merge any extra, the keys are not right or something.Arthur My Blog

Can you post a pic of the Sort editor for Sort 4? Talk to me now on

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

This one is the editor:http://i55.tinypic.com/2mrwhow.jpg Advanced editor (input columns): http://i51.tinypic.com/2vmeu4o.jpg

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

I see the number of rows. How can I do to attach an image?