Sort the dataset in the dataflow – Sort Transformation – SSIS
Ordering data is the most important task of every database engine. It helps the system to identify or search the require data in the huge dataset or database quickly. It also prepare the dataset in such a way that we can read it in alphabetical order (A-Z, Z-A) and in sometimes numbers in serial order (0-9, 9-0).
Sort Transformation task get an input of a data set and sort the dataset with ascending or descending order and it also includes numbers. This task has one input and one output.
We can set the sort order for each column in the data flow. For instance, if we have a dataset with customer and geography then our report requires data in the order of country-State-City-Customer in ascending order. To achieve this, we can set the column sort order number from 1 to n (ascending order) for each column. We specify -1 to -n for descending order.
The most exciting feature is that we can remove duplicate row in the dataset using Sort Transformation.
Another useful feature is that we can configure the Sort task to ignore case when using character datatype. For instance, in typical sort order of A comes after a, which means the lower case string has the higher priority than upper case characters.
We can change the order of the data using sort type column option in the sort transformation editor. We can sort our data in ascending or descending order.
Lear more about the Sort Transformation.
Step 1: Create a package and add a data flow task. Now, go to data flow design interface.
Step 2: Add Excel data source and connect with a excel workbook as shown in the below image.
Just have a look at the 2 & 3 row.
Step 3: Add Sort task and connect Excel data source task with it.
Step 4: Open Sort Transformation Editor. Select employee name column for the sort. Sort transformation assigns sort order 1 for the column. If you have more columns to sort you can give the sort order as discussed in this post.
Step 5: Click OK and Add a Union all transformation and conned sort transformation output with it. To view result add data viewer component.
Step 6: Execute package now.
Now look at the output dataset. The order of the row 2 & 3 has changed even-though both alphabets are same. Sorry, There is a difference in the case of the alphabets. As I told about the priority of the sort by case. Lowe case always be the first candidate.
If you wish to change the behavior of the sort then open Sort Transformation Editor and change the comparison flag of the sort column.
After this change execute the package one more time. What will be the output?
Thanks for reading.