March 26, 2015 at 1:42 pm
Hi Guys,
Here is my simple SSIS Package, I am using two different Source, however both are SQL Source then I have to use Derived column transformation to clean and do other things, then I want to use merge Join transformation as you all know your linking column should be Sorted, I know I can use use SORT TRANSFORMATION (expensive transformation) or right click on source and from advance properties change the sort key to 1. Here is my question,
My linking column is good after derived column, how i can sort that key without using Sort transformation?
Please advise.
Thank You.
March 30, 2015 at 2:06 am
Why don't you join directly in the source component?
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
March 30, 2015 at 11:24 am
My Linking key is not ready in source, that's why I am using Derived column to make ready my Linking key for linking...
It is just a look up table, however I have to use heavy T-SQL to generate look up table for linking....
Thank You.
March 30, 2015 at 11:45 am
I agree with Koen that, if it's an option, doing the join in the source will give you the best results. However, if that's not an option, you can manually set the sort property to indicate the column on which the data is sorted. However, don't do this unless you're certain that the data truly is sorted by that column. If you mark a column as the sort column and then send unsorted data to the merge join transformation, it's going to cause you some problems.
Tim Mitchell, Microsoft Data Platform MVP
Data Warehouse and ETL Consultant
TimMitchell.net | @Tim_Mitchell | Tyleris.com
ETL Best Practices
March 30, 2015 at 11:53 am
So Correct me If I am wrong or I understand right.... there is no choice for me, I have to use SORT Transformation in my case, right?
Thank You.
March 30, 2015 at 12:02 pm
If you are unable to sort the data in the source, yes, you would have to sort the data in the data flow before you can use the merge join transformation.
Stepping outside the problem a bit, do you have to use the merge join transformation? Could you instead load the data (with the sort keys you're generating) to staging tables, and then join those tables together in a SQL statement? If you can do that, you'll avoid the expensive sort in the data flow.
Tim Mitchell, Microsoft Data Platform MVP
Data Warehouse and ETL Consultant
TimMitchell.net | @Tim_Mitchell | Tyleris.com
ETL Best Practices
March 30, 2015 at 2:08 pm
Regarding the MERGE JOIN, does it do a inner, left or full join?
If inner, can there be multiple matches? If there aren't, you can use a lookup instead.
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
March 30, 2015 at 2:10 pm
In my case, I am using L.O.Join...
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply