SSIS 2008 - Sort Transformation

SORT Transformation:

Name itself tells us the use of this Transformation. This transformation requires one input and provides one output. It does not support ‘Error Routing’. Along with sorting the data, this transformation also provides the option to ‘Remove duplicate records based on the sort keys’. Based on the options selected, it can behave as a Synchronous or Asynchronous transformation.

As a Synchronous transformation: If this transformation is configured only to sort the data, then it will behave as a Synchronous transformation, whereby not changing the number of records it read and provided as output.

As a Asynchronous transformation: Along with sorting the data, if this transformation is configured to ‘Remove duplicate records based on Sort key’, then this will behave as a Asynchronous transformation, whereby the number of records given as output will be less than what it read (if there are any duplicates).

Below example will walk you through the various options available in this transformation:

Sample data:

Col_A	Col_B	Col_C
1	600	A
1	100	a
2	200	b
3	300	c
4	400	d
5	500	e
2	700	B
3	800	c
4	900	d
5	1000	e

1. Sorting data using key column having Numeric data type:
For this we will use Col_A from the sample data, which is having Numeric data type. Below shown is the Sort transformation editor screenshot configured to use Col_A as the ‘Sort Key’:
Output:

2. Sorting data using key column having String datatype:
For this we will use Col_C from the sample data, which is having Numeric data type. Below shown is the Sort transformation editor screenshot configured to use Col_C as the ‘Sort Key’:
Output:
Using String datatype as SORT key makes one more option available in the editor named ‘Comparison Flag’. Below screenshot shows the various options available in it:
From the available options we will use ‘Ignore Case’.
Output:
If you compare this output with the earlier output, you will see the difference:
Sample data:

Output without ‘Ignore Case’:Output with ‘Ignore Case’:3.  Eliminating duplicates:For this we will use Col_A from the sample data. Below shown is the Sort transformation editor screenshot configured to use Col_A as the ‘Sort Key’ and option ‘Remove rows with duplicate sort values’ been checked:Output: