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:

• Common Properties
• Transformation Custom Properties

For more information about how to set properties, click one of the following topics:

• How to: Set the Properties of a Data Flow Component Using a Component Editor
• How to: Set the Properties of a Data Flow Component in the Properties Window
• How to: Set the Properties of a Data Flow Component Using the Advanced Editor

See Also

Concepts

Creating Package Data Flow
Integration Services Transformations

Help and Information

Getting SQL Server 2005 Assistance

Change History

Release	History
15 September 2007	New content: Added note about how the Sort transformation sorts GUIDs differently than the ORDER BY clause in Transact-SQL.
5 December 2005	New content: Added information about using property expressions with the MaximumThreads custom property.