Share via

  • Article

Sort Transformation

The Sort transformation sorts input data in ascending or descending order and copies the sorted data to the transformation output. You can apply multiple sorts to an input; each sort is identified by a numeral that determines the sort order. The column with the lowest number is sorted first, the sort column with the second lowest number is sorted next, and so on. For example, if a column named CountryRegion has a sort order of 1 and a column named City has a sort order of 2, the output is sorted by country/region and then by city. A positive number denotes that the sort is ascending, and a negative number denotes that the sort is descending. Columns that are not sorted have a sort order of 0. Columns that are not selected for sorting are automatically copied to the transformation output together with the sorted columns.

The Sort transformation includes a set of comparison options to define how the transformation handles the string data in a column. For more information, see Comparing String Data.

Note

The Sort transformation does not sort GUIDs in the same order as the ORDER BY clause does in Transact-SQL. While the Sort transformation sorts GUIDs that start with 0-9 before GUIDs that start with A-F, the ORDER BY clause, as implemented in the SQL Server Database Engine, sorts them differently. For more information, see ORDER BY Clause (Transact-SQL).

The Sort transformation can also remove duplicate rows as part of its sort. Duplicate rows are rows with the same sort key values. The sort key value is generated based on the string comparison options being used, which means that different literal strings may have the same sort key values. The transformation identifies rows in the input columns that have different values but the same sort key as duplicates.

The Sort transformation includes the MaximumThreads custom property that can be updated by a property expression when the package is loaded. For more information, see Integration Services Expression Reference, Using Property Expressions in Packages, and Transformation Custom Properties.

This transformation has one input and one output. It does not support error outputs.

Configuring the Sort Transformation

You can set properties through the SSIS Designer or programmatically.

For more information about the properties that you can set in the Sort Transformation Editor dialog box, see Sort Transformation Editor.

The Advanced Editor dialog box reflects the properties that can be set programmatically. For more information about the properties that you can set in the Advanced Editor dialog box or programmatically, click one of the following topics:

For more information about how to set properties, see How to: Set the Properties of a Data Flow Component.

External Resources

Sample, SortDeDuplicateDelimitedString Custom SSIS Component, on codeplex.com.

Integration Services icon (small) Stay Up to Date with Integration Services

For the latest downloads, articles, samples, and videos from Microsoft, as well as selected solutions from the community, visit the Integration Services page on MSDN or TechNet:

For automatic notification of these updates, subscribe to the RSS feeds available on the page.