Unfortunately when you have a sorted result set in SSIS using T-SQL this sorting is not automatically picked up in the metadata. This subsequently causes additional issues with components like the Merge Join that expect the result set to be sorted and the metadata to pick this up. Because of this fact there are two additional steps that must be performed in the OLE DB Source.
- Add your sorted select statement to your OLE DB source.
- Right click and go to Show Advanced Editor.
- Click the last tab on top, “Input and Output Properties”.
- Click OLE DB Source Output
- Change the IsSorted property to True, the default is false.
- Now expand the tree view for “OLE DB Source Output” and expand “Output Columns”
- Find the column that you sorted in your result set and assign it a SortKeyPosition of 1.
Thanks for looking.