How to use Merge/Merge Join Transformation Without SORT Tranformation.

Merge/Merge Join transformation need to be preceded by a Sort Transformation.However it is a good SSIS Practise to sort the data at the source.Sort Transformations can slow down the ETL process when dealing with large volumes of data.

Assuming that the data source is an OLE DB Source a simple ORDER BY clause in the SQL statement of the Source will sort the data at the source itself.

In order to bypass the SORT Transformation 2 properties need to be configured on the source
IsSorted
SortKeyPosition

The above mentioned 2 properties will be configured through the advanced source editor 

Once the IsSorted property is set to TRUE Configure the SortKeyPosition.To configure the sortkeyposition go to the output columns

Select the column on which the data is sorted in the source and set the SORTKeyposition to 1

This configuration will tell the data flow engine that the data has already been sorted by the emp_id in the source.