SSIS Sort Transformation

The Sort transformation is mostly like ORDER BY clause in SQL statement to sort data. It has one input as data source and one output to export sorted data. There is no error outputs for this transformation. You can sort data by one column or a couple of them and the sort type is either ascending or descending for each column. The sorted columns have orders to sort the data. Only when the first sort columns of the data are the same, the second sorted column starts to be used to compare data.

The data source is still the Excel file C:\SSIS\Books.xlsx which was changed in the previous section.

[Excel Data Books]

We'll sort the data by price and book name and the record with the same sort key can be removed if we make a proper setting.

  1. Open the package Sort.dtsx if it is closed and click Data Flow button.

  2. Delete the path of Data conversion output, drag & drop the Sort transform under the "Data Conversion" and link them as shown below.
  3. [Add Sort Transform]

  4. Right-click the Sort transform and choose "Edit..." to open the Sort Transformation Editor and set it as shown below.
  5. [Sort Transformation Editor]

    We choose "Price" as the primary sort column and "Book Name" as secondary one with descending type. Because the Book Name is string-typed, "Ignore case" is chosen in the Comparison Flags. At last, we have "Remove rows with duplicate sort values" checked.

  6. Click OK button to close the editor.
  7. The red error icons in the data flow will be removed.

  8. Add a data viewer above the Sort transform and another below it. Then run the package.
  9. The data in first data viewer will be shown up first with original sequence.

  10. Click the Green arrow in the viewer and second viewer will be shown up.
  11. [Data Viewer is Shown Up]

    The record with the same sort columns was removed in the second viewer.

  12. Click the Green arrow in the second one and the execution is over.

In summary, the Sort transform is not only sorting the data by the selected columns but also removing the records with the same value of the sorting columns.