When using a Merge or Merge Join component in a data flow, your incoming data is required to be sorted. While it may be easy to drop a Sort component into your data flow, it may make more sense to perform the sort in your source query (if you are using a source such as an OLE DB Source component).
If you decide to use an ORDER BY in your source and want to tell the SSIS Data Flow that your data is sorted, follow the below steps:
First, ensure that your data is being sorted with the ORDER BY clause. Make note of which column(s) are in the sort and their positions.
Second, open the Advanced Editor for the OLE DB Source component by right-clicking on the OLE DB Source and selecting “Show Advanced Editor…”
Then, click on the “Input and Output Properties” tab.
Then highlight the “OLE DB Source Output Properties” branch and set the “IsSorted” property to True.
Next, expand the “OLE DB Source Output Properties” branch. From here, select the first column under “Output Columns” that corresponds to the first column in your ORDER BY clause. Set its “SortKeyPosition” property to the number 1.
Repeat the last step for each column in your ORDER BY clause, advancing the SortKeyPosition by one each time. (1,2,3,…)
That’s it. Now the SSIS Data Flow will understand and acknowledge that the incoming data is sorted and will not require a Sort Component between the source and a Merge Join component, for instance.