How to avoid unnecessary sorting in SSIS Package

Introduction

     In this article we will explain about what is the reason to remove the unnecessary sort transformations in the pipeline (work flow in Data flow task).

Solution

     Sort transformation is one of the blocking components in dataflow transformations. It forces the pipeline to “wait” for all the data and then sorts the respective data from the data source. We will explain brief description as follows. 

Example

• Transfer lakh’s of data from two tables to one table in sorted order.

• In the above diagram its show to take data from two data sources and by using sort transformation to sort the data and merges the data by Merge transformation and finally the sorted in OLEDB destination.

• After that we remove the Sort transformations in the package.

• Right click on the OLEDB source and select “Show Advanced Editor …”

• Go to the Input and Output Properties tab and in the OLE DB Source Output set the IsSorted property value to True in Common properties.

• Set the BusinessEntityID Column with SortKeyPosition property value as 1.

• Again runs the same package with above changes done successfully.

            

              Check the Elapsed time in the two cases. So speed gain is quite large when we are not using Sort transformations.

Let me know what you think about this article.