Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Difference between IsSorted and Sort transformation Expand / Collapse
Author
Message
Posted Friday, February 15, 2013 10:58 AM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Monday, October 13, 2014 11:28 AM
Points: 99, Visits: 309
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
Posted Friday, February 15, 2013 12:22 PM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Yesterday @ 2:42 PM
Points: 224, Visits: 1,732
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
Posted Friday, February 15, 2013 1:46 PM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Monday, October 13, 2014 11:28 AM
Points: 99, Visits: 309
Thanks.
Post #1420744
Posted Saturday, February 16, 2013 3:35 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

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
Posted Monday, February 25, 2013 7:55 AM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Monday, October 13, 2014 11:28 AM
Points: 99, Visits: 309
It does. Thank you.
Post #1423618
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse