How use Merge Join Transformations without Sort component in SSIS

One of the main issue in Merge Join Transformation is data should be sorted for the inputs. This sorting will be create a huge performance issue when the input data is large size or insufficient memory.

Instead of Sort component, data can be sorted in Ole DB Source component. The below example shows that how can we overcome this issue.

In this Sort operation, instanceid and quiz are sored as in the Order 1 and 2.

Remove this Sort component and perform the sort in Ole DB Source component as follow by order:

1. Introduce ORDER BY instanceid, quiz in source query, so that order will happened in the SQL Engine level.
2. Right click on Ole DB Source component and click on Advanced Editor and change IsSorted=TRUE as show below image.

3. Click on Sorting Output column and change sort order as per required as shown below.

4. Connect to Merge Join Transformations as Input.

Cheers! Uma