Wikis - Page Details

First published by Zoltán Horváth (1Microsoft Partner, Microsoft Community Contributo)
When: 9 Feb 2012 5:33 AM
Last revision by Richard Mueller (cMVP, Microsoft Community Contributo)
When: 5 Apr 2013 7:42 AM
Revisions: 15
Comments: 8

Options

Can You Improve This Article?

Positively! Click Sign In to add the tip, solution, correction or comment that will help other users.

Report inappropriate content using these instructions.

SSIS Sort Transformation

Integration Services uses transformations to manipulate data during an ETL dataflow. Transformations can be used in Data Flow Tasks, between data source and destination components, or other transformations.

The Sort transformation basically has only one task: to put the incoming rows in a specified order to the output. The Sort has only one input and one output.
The Sort transformation can be used for removing duplicates from a dataset as well: all rows which have duplicate sort values will be omitted from the output, if you select the "Remove rows with duplicare sort values" option in the editor of the transformation.

Here is an example screenshot of the Sort Transformation Editor:

In the example above, the output will not contain the ID column (only the Date and Label columns), and it will be ordered by the values of the Date column. Every row will be included, even if duplicates are found in the Date column.

The Sort transformation is a blocking one: since all input rows are needed to get the rows in the desired order, the flow of data will stop until this transformation ends the calculation. This applies to deduplication as well, since at least one column must be selected for sorting.

Sort is mostly used before the Merge Join transformation (which accepts two sorted inputs only).

For more detailed information on this transformation, please refer to this MSDN article.
For the complete list of SSIS transformations, check this Wiki article.