“Merge Join” is a partially blocking component and it requires the source is sorted.
There are two ways to get the source :
1. Use “Sort” component. — Sort is a full blocking component.
2. Sort the source in SQL and mark “IsSorted”
Because Sort is a full blocking component, So I think the second one will get better performance.
I keep the two source sorted, and got one Error:
The IsSorted property must be set to True on both sources of this transformation.
I can not find this property in the properties or Editor, that’s why I hate SSIS sometimes.
At last I got it from “Advanced Editor…”:
and that’s not enough, we need to specify the SortKeyPosition:
Sort Data for the Merge and Merge Join Transformations http://msdn.microsoft.com/en-us/library/ms137653.aspx
SQL Server Integration Services SSIS Design Best Practices :
// Proudly powered by Apache, PHP, MySQL, WordPress, Bootstrap, etc,.