The Sort Transformation in SSIS is used to sort the source data in either Ascending or Descending order, which is similar to the T-SQL command ORDER BY statement. Some transformations like Merge Transformation and Merge Join Transformation needs data to sort 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 a positive number, then Sort Transformation sort the data in Ascending order
- If the Sort Order value is Negative number, SSIS Sort Transformation sort the data in descending order
For example, if Employee Name column has a 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 the toolbox to the 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 the OLE DB source to Sort Transformation to perform transformations (sorting) on the source data.
STEP 5: Double click on SSIS Sort Transformation to configure it.
Check the columns we want to sort, and don’t forget to Pass Through the 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 the 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 the same as xyz.
- Ignore Kana Type: Specify whether you want to differentiate between the Japanese language: hiragana and katakana letters. If we check this option, then it will ignore the 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 a single byte and a double-byte representation of the same character. If we check this option, SSIS 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, both %xyz is the same as xyz.
- Sort punctuation as symbols: If we check mark this option, all the punctuation symbols except the hyphen and apostrophe sorted before the actual letters. For instance, SSIS Sort Transformation will sort ? xyz before x.
Remove rows with duplicate sort values: If you checkmark 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 the 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, we selected the [Sort Transformation in SSIS] table in [SSIS Tutorials] Database
STEP 7: Click on the Mappings tab to check whether the source columns 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