Knight's Microsoft SQL Server 2012 Integration Services 24-Hour Trainer

Chapter 23

Ordering Data with the Sort Transform

The Sort Transform enables you to sort data based on any column in the Data Flow path. To configure the Sort Transformation Editor after it’s been connected, open the transform and check the columns you need to sort by (Figure 23-1). Uncheck any columns you don’t want passed through the path from the Pass Through column. By default, every column passes through the Data Flow pipeline.

Figure 23-1

You can optionally check the Remove rows with duplicate sort values option. When this is checked, if a second value comes in that matches your same sort key, it is disregarded, and the row is dropped.

NOTE The Sort Transform is a fully blocking asynchronous transform and will slow down your Data Flow performance. Use these only when you have to, such as for sorting a Flat File Source, and sparingly.

Sorting data in SSIS is one of the most frequently required operations. This is because many other transforms that can be used require that data be presorted with either a Sort Transform or an ORDER BY statement in the OLE DB Source. You should avoid using the Sort Transform when you can because of speed constraints.

If you place an ORDER BY statement in the OLE DB Source, SSIS is not aware of the ORDER BY statement because it can just as easily have been in a stored procedure, so you must notify SSIS that the data is presorted. To do ...