Sort Transformation in SSIS with Example

Friends,

This is one of the transformations which doesn’t need much explanation. Sort is used to SORT the input data in ascending or descending order. You can apply sort on multiple columns of the input data. Let us see an example of the same.

• Open a new project and drag a Data Flow task from toolbox in Control Flow.
• Edit the Data Flow task by double clicking the object or by selecting EDIT button on Right click on the object. Make sure the Data Flow Page is opened as shown below.

• Select OLE DB data source from data flow sources and drag and drop it in the data flow.
• Double click on the OLE DB data source to open a new window where we can set the properties of the connection.
• Select the connection manager and click on new button to set the connection string as shown below.

• Set the connection to the database by providing the Server name,database name and authentication details if required.
• After the connection is set, select Data Access mode as “Table or View” as shown below and then select the table which we created just now.

• Drag and drop Sort Transformation into the Data Flow pane and provide connection between OLE DB Source and Multicast Transformation.
• Now select the transformation and edit it for setting sort properties. Select the column names on which we are gonna sort the data.

• You can use Sort Type option to SORT the data either in Ascending or in Descending order. Sort Order is set to give an order to the columns when we have more than one in SORT transformation.
• Now drag and drop OLE DB Destination and give the output of Sort transformation and as input to the OLE DB destinations, then EDIT the destination transformation to set the connection properties.
• Once the Destination is SET then trigger the package and you can see the data ordered in the order specified in SORT Transformation.
• If we have same value more than once and if you wish to eliminate that duplicate then you can make use of the option “Remove rows with Duplicates values” in Sort transformation.

• Now if you trigger the package, you can see the duplicates will get removed as shown below.

This is how we can use Sort transformation to SORT the data in any order. You can also hide the duplicate records if we have any in the column specified in Sort Transformation.

Happy Coding !!!

Regards,
Roopesh Babu V