SSIS Sort Transformation

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.
Leave a Comment
  • Please add 3 and 7 and type the answer here:
  • Post
Wiki - Revision Comment List(Revision Comment)
Sort by: Published Date | Most Recent | Most Useful
Page 1 of 1 (1 items)
Wikis - Comment List
Sort by: Published Date | Most Recent | Most Useful
Posting comments is temporarily disabled until 10:00am PST on Saturday, December 14th. Thank you for your patience.
  • Zoltán Horváth edited Original. Comment: adding deduplication info and example screenshot

  • Zoltán Horváth edited Revision 1. Comment: rephrasing

  • Zoltán Horváth edited Revision 7. Comment: clarifying Merge Join comment

  • Zoltán Horváth edited Revision 8. Comment: Adding info about deduplication in the buffer usage part

  • Zoltán Horváth edited Revision 9. Comment: Note to myself: I must type slower or at least triple-check... Correcting typo.

  • Hasham Niaz edited Revision 10. Comment: Revision, checking Wiki updates are counting properly

  • Revision, Checking Wiki Updates are Logging properly

  • Zoltán Horváth edited Revision 12. Comment: Clarifying deduplication

Page 1 of 1 (8 items)