Using merge join without Sort transformation

Merge join without SORT Transformation

Merge join requires the IsSorted property of the source to be set as true and the data should be ordered on the Join Key. So when we add a SORT transformation it sets the IsSorted property of the source data to true and allows the user to define a column on which we want to sort the data ( the column should be same as the join key). Now to avoid the using SORT transformation we need to set the metadata of the source properly for successful processing of the data else we get error as IsSorted property is not set to true.

We will try to join two tables Department and Employee on DeptID column without using SORT transformation in our SSIS package.

Department Table details

Employee Table details

Steps in SSIS package

• Create a new package and drag a dataflow task.
• Now right click on Data-flow and click on edit, the data-flow container opens.
• First task is to create a connection to the database.   

• Now we need to set department source, first we will drag the OLEDB Source and set connection and  table

• Now we click on "ok" to save changes. Again we right click on department source and click on "Show Advance Editor" and go to "input output property" tab. On the "input and output property" tab we select the "OLEDB Source output" and set the IsSorted property to true as mentioned below.

• After setting the above property we expand the "output column" and inside that we select the column (DeptID) which we will use in join and set SortKeyPosition = 1 in the properties tab as shown in below image.

• We will repeat the same process for second source i.e the Employee table.

Once the properties set for the sources we drag the merge join transformation and set the required properties which include the sources, join key and the output columns as shown below.

Now we drag the out put of the Merge join and join to a Derieved column where in the path we also configure data viewer to check the data. Now out package Data-Flow looks like the below image.

We run the package and try to view the data which shows like below.