Difference between IsSorted and Sort transformation

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » SQL Server 2005 » SQL Server 2005 Integration Services » Difference between IsSorted and Sort...

Difference between IsSorted and Sort transformation

Rate Topic

Display Mode

Topic Options

Author

Message

rs80

Posted Friday, February 15, 2013 10:58 AM

Valued Member

Group: General Forum Members
Last Login: Monday, April 29, 2013 11:46 AM
Points: 72, Visits: 223

Hi Everyone,

I have 2 data sources that get merged using a merge join. I set the IsSort property and SortKey on both data sources, but didn't get the correct number of records in an inner join. When I used the sort transformation I got the correct number of records in an inner join. So what's the difference between the both of them? Can I only do an IsSort when the data is sorted in the two sources?

Thanks!

Post #1420684

e4d4

Posted Friday, February 15, 2013 12:22 PM

SSC-Enthusiastic

Group: General Forum Members
Last Login: Today @ 1:54 PM
Points: 151, Visits: 1,039

IsSort tell SSIS that a source is sorted eg: via order by or external program that generated a file.
Sort transformation is performing sort.
So if you tell SSIS that you have sorted the source and it isn't sorted, then this can cause the wrong result.

Post #1420726

rs80

Posted Friday, February 15, 2013 1:46 PM

Valued Member

Group: General Forum Members
Last Login: Monday, April 29, 2013 11:46 AM
Points: 72, Visits: 223

Thanks.

Post #1420744

mohit_3454

Posted Saturday, February 16, 2013 3:35 AM

Grasshopper

Group: General Forum Members
Last Login: Tuesday, February 19, 2013 7:10 AM
Points: 15, Visits: 53

You need to take care of two things while using Merge Join in SSIS :
1) Is Sorted is set to "True" and Sort Key is defined on the sort column
2) The data set (in the source query) needs to be actually sorted on the Sort Key column above.
Only then you will be able to get correct results.

Hope this helps .. :)

Post #1420879

rs80

Posted Monday, February 25, 2013 7:55 AM

Valued Member

Group: General Forum Members
Last Login: Monday, April 29, 2013 11:46 AM
Points: 72, Visits: 223

It does. Thank you.

Post #1423618

« Prev Topic | Next Topic »

Permissions