MERGE TRANSFORMATION

What does the Merge Transformation do?
The Merge Transformation merges two sorted data sets and outputs a sorted data set – and by Merge it really means a “Sorted Union All”. You provide it with the two sorted sets, specify the keys on which they are sorted, and the “merge” then takes place, ordering the output on those keys. In my example package.The sort keys must have the property IsSorted set to True – this will be done automatically by the Sort component, but will need to be set manually in the source if the data is sorted outside of SSIS – this is available in the advanced properties of the output columns of a component.

When would you use the Merge Transformation?
Technically, it should be used to merge two sorted datasets to get a sorted merged output data set. In practice, I fail to see much advantage in using this component -The only scenario I can see this being genuinely useful is when you have to Union two very large sorted datasets, need the output to keep that sort, and cannot afford the memory overhead of a blocking Sort component after the Union which will have to recieve all the data before it can do that sort

Print this post