The Sort Transformation in SSIS is used to sort the source data in either Ascending or Descending order, which is similar to T-SQL command ORDER BY statement. Some transformations like Merge Transformation and Merge Join Transformation needs data to be sorted before using them. In these situations we use SSIS Sort Transformation to sort the data.
Sort Transformation in SSIS uses the Sort Order property to specify the priorities.
- If the Sort Order value is positive number then Sort Transformation sort the data in Ascending order
- If the Sort Order value is Negative number then Sort Transformation sort the data in Descending order
For example, if Employee Name column has sort order of 1 and Salary column has sort order 2. Sort Transformation will sort the data by Employee Name and then sort by city.
Sort Transformation in SSIS Example
STEP 1: Drag and drop the data flow task from the toolbox to control flow and rename it as Sort Transformation in ssis.
Double click on it and it will open the data flow tab.
STEP 2: Drag and drop OLE DB Source, Sort Transformation and OLE DB Destination from toolbox to data flow region
STEP 3: Double click on OLE DB source in the data flow region will open the connection manager settings and provides space to write our SQL statement.
SQL Command we used in the above screenshot is:
SELECT [Color] ,[EnglishProductName] ,[ListPrice] ,[DealerPrice] ,[EnglishDescription] ,[StartDate] ,[EndDate] FROM [AdventureWorksDW2014].[dbo].[DimProduct] WHERE [DealerPrice] IS NOT NULL
STEP 4: Click on columns tab to verify the columns. In this tab we can uncheck the unwanted columns also.
Click ok and Drag the blue arrow from OLE DB source to Sort Transformation to perform transformations (sorting) on the source data.
STEP 5: Double click on Sort Transformation to configure it.
Check the columns we want to sort and don’t forget to Pass Through remaining column. If you forget to select the Pass Through then they won’t appear in output columns.
To understand the Sort Transformation in ssis, we are sorting one column with Ascending order and another with Descending order. From the above we are sorting the data by Color and then by [English product name] because we specified the Sort order in that way.
- First data is sorted by Color in Ascending Order and then
- Second, data is sorted by English product name in Descending order.
From the above screenshot you can observe that, Sort Transformation in ssis has one more important property called Comparison Flags.
- Ignore case: Specify whether you want to differentiate between uppercase and lowercase letters. If we check this option then both XYZ is same as xyz.
- Ignore Kana Type: Specify whether you want to differentiate between Japanese language hiragana and katakana letters. If we check this option then it will ignores kana Type.
- Ignore nonspacing characters: If you don’t want to differentiate between the normal characters and diacritics then check this option.
- Ignore Character Width: Specify whether you want to differentiate between single bite and double byte representation of same character. If we check this option then Sort Transformation ignores the difference.
- Ignore Symbols: Specify whether you want to consider the normal letters and letters with symbols (such as white spaces, currency symbols, operators etc) as same or not. If we check this option then both %xyz is same as xyz.
- Sort punctuation as symbols: If we check mark this option then all the punctuation symbols except the hyphen and apostrophe will be sorted before the actual letters. For instance, Sort Transformation will sort ?xyz before x.
Remove rows with duplicate sort values: If you check mark this option then, Sort Transformation will remove the duplicate columns. If not, then this transformation will copy all the columns including duplicate rows.
STEP 6: Drag the blue arrow from Sort Transformation to OLE DB Destination.
Now we have to provide Server, database and table details of the destination. So double-click on the OLE DB Destination and provide the required information
From the above screenshot you can see that, We selected the [Sort Transformation in SSIS] table in [SSIS Tutorials] Database
STEP 7: Click on Mappings tab to check whether the source columns are exactly mapped to the destination columns.
Click ok to finish designing the SSIS Sort Transformation package. Let us run the package
Let us open the SQL Server Management Studio and check the results
Thank You for Visiting Our Blog