SQL Server Integration Services (SSIS) - Merge Transformation (Setting Sorting) in SSIS

Introduction:

In this article we are going to see how to set the sorting properties of the merge transformation. In my previous article you can see the steps for how to use the merge transformation. To set the transformation merge we need to follow some setting which should be taken in the Source data source. Let's jump start to see this sample of how to set the properties of the sorting.

You can look into my series of articles on SSIS at the url - http://f5debug.net/all-articles/

Steps:

Follow steps 1 to 3 in my first article to open the BIDS project and select the right project to work on an integration services project. Once the project is created, we will see how to use the Merge Transformation task. Once you open the project just drag and drop the Merge transformation control and a source provider as shown in the previous article. Follow the steps until adding the merge control with the input data sources. Your screen will look like below:



You can see some executing symbol shows on the data source as shown in the above screen and in the merge transformation you can see it's showing the failed execution process.

Now right click on the data source (Merger Source 1) and select "Show Advanced Editor" as shown in the screen below:



Now move to the Input and output properties as shown in the screen below and select the IsSorted = true as shown below:



Now click on the + symbol and go to Output columns and select SortkeyPosition to 1 as shown in the screen below:



Now click on ok and go to the main screen; you can see that Data Source 1 is configured correctly and the second one is still showing the execution failure message as shown below:



Do the steps configuration which we did for Data source 2 as well and you can see the result set as shown below:



Now you are done with the steps and the sorting properties as enabled and ready for execution.

Conclusion:

So in this article we have seen how to use the Merge Transformation (Sorting properties) task and the key configurations used in order to use this task easily.