Knight's 24-Hour Trainer: Microsoft® SQL Server® 2008 Integration Services

Chapter 19. Ordering Data with the Sort Transform

The Sort Transform allows you to sort data based on any column in the 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 19-1). Uncheck any columns you don't want passed through the path from the Pass Through column. By default, every column passes through the pipeline.

You can optionally check the "Remove rows with duplicate sort values" box. 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 and sparingly.

The Sort Transform is a fully blocking asynchronous transform and will slow down your Data Flow performance. Use these only when you have to and sparingly." The Sort Transform is one of the most frequently used transforms. This is because many other transforms that can be used require data to 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 have easily been in a stored procedure, so you must notify SSIS that the data is presorted. To do this right-click the source and select Advanced ...