The SSIS transformations are the data flow components that are used to perform aggregations, sorting, merging, modifying, joining, data cleansing, and distributing the data.
Apart from these, there is an important and powerful transformation in SSIS called Lookup transformation to perform lookup operations. In this article, we will show you the list of available SSIS transformations and explains their working functionality.
SSIS Transformations with Examples
The following are a list of transformations available in SQL Server Integration Services. I suggest you click on the URL (red color links) to navigate to the required transformation and find the example with a detailed explanation.
Business Intelligence Transformations in SSIS
The following list of SSIS transformations will perform Business Intelligence operations such as Data Mining, Correcting, and cleaning the data
| SSIS Transformations | Description |
|---|---|
| Slowly Changing Dimension | This transformation coordinates the inserting and updating records in data warehouse dimension tables. It supports Type 0, Type 1, and SCD Type 2 |
| Fuzzy Grouping Transformation | It is used to replace the wrongly typed words with correct words, by grouping technique. |
| Fuzzy Lookup Transformation | This SSIS transformation uses fuzzy matching to find one or more close matches present in the reference table and replace the source data with reference data. |
| Term Extraction Transformation | It is used to extract terms from the text present in the source data. This transformation provides four different options. And they are: Extract Nouns Only, Extracting Noun Phrases Only, Extract Nouns and Noun Phrases, and Exclusion Tab |
| Term Lookup Transformation | It uses the reference table to find the matched sentences in the source data and counts the number of times a term repeated in the source. |
| Data Mining Query Transformation | This SSIS transformation helps us to run data mining queries. |
| DQS Cleansing Transformation | It is used to correct the source data by applying the rules that we created for the data source. |
Row Transformations in SSIS
The below list of SSIS transformations is useful to update the existing column values and to create new columns.
| SSIS Transformations | Description |
|---|---|
| Character Map Transformation | It is useful to transform input characters to uppercase, lowercase, etc. |
| Copy Column Transformation | It simply duplicates the source columns. Just like copying the column data, and pasting it in a new column. |
| Data Conversion Transformation | This transformation is used to convert the data from one data type to another data type. It is similar to CAST or CONVERT |
| Derived Column Transformation | It helps us to deal with SSIS expressions |
| Export Column Transformation | It is handy to export images, binary files, media, or any large documents from SQL Server to file system. |
| Import Column Transformation | This transformation is used to import data from the text file to the data flow. Do some manipulations, and then forward the data to the destination. |
| Script Component | It provides an option to perform functions that are not available in the SSIS toolbox. You can use this as the Data Source, or Transformation, or Destination. |
| OLE DB Command Transformation | It is used to run the SQL Statements like INSERT, UPDATE, and DELETE Statements in the Data Flow. Please refer to Insert Data, Delete Data, and Update Data articles to understand the operations. |
Rowset Transformations in SSIS
The below list of SSIS transformations will create a new rowsets. And that rowsets may include aggregated data, Sorted data, or Pivoted and Unpivoted data.
| SSIS Transformations | Description |
|---|---|
| Aggregate Transformation | This SSIS transformation performs the aggregations such as SUM, MIN, AVG, etc |
| Sort Transformation | It is used to sort the data source in either Ascending or Descending order. It is similar to the T-SQL command ORDER BY statement. |
| Percentage Sampling Transformation | This SSIS transformation passes the given percentage of rows from the data source to selected output rows. And the remaining rows will be transferred to unselected output rows. |
| Row Sampling Transformation | It provides an option to specify the number of rows you want to retrieve from the data source. |
| Pivot Transformation | It is used for converting individual row data into separate columns, which is similar to SQL PIVOT. For SQL Server 2008 R2 users, I suggest you refer Pivot Transformation in 2008 R2 as well. |
| Unpivot Transformation | It is used to convert the DE Normalized data into normalized data. It is similar to SQL UNPIVOT |
Split and Join Transformations
The following list of transformations is useful to distribute the source data to different outputs. And also to join multiple outputs as a single output.
| SSIS Transformations | Description |
|---|---|
| Conditional Split Transformation | It is like the IF or CASE statement, which checks the given condition, and based on the result, the output will send to the appropriate destination path. |
| Multicast Transformation | This SSIS transformation sends input data to multiple destination paths without any modification. |
| Union All Transformation | It is used to combine data from multiple sources (such as excel files, flat file, or multiple SQL tables), and produce one output |
| Merge Transformation | This SSIS transformation is used to merge two inputs, such as tables or files, and provide one output. |
| Merge Join Transformation | The Merge Join Transformation is useful to perform SQL Joins such as Inner Join, Left Outer Join, Full Outer Join and Right Outer Join (achieved by Swapping the tables) |
| Lookup Transformation | This transformation is used to compare source data with existing data present in the reference table (or Lookup Table) to find matching ones |
| Cache Transformation | This SSIS transformation writes data from a connected data source to a Cache file (.caw extension) using Cache Connection Manager. It is beneficial to perform Lookup on the data present in the cache file. |
SSIS Audit Transformations
Below shown SSIS transformations helps to add Audit information and the Row count data
| SSIS Transformations | Description |
|---|---|
| Audit Transformation | It is used to find the auditing information such as Machine name, package name, id, date, etc |
| Row Count Transformation | This transformation counts the rows passed through a data flow, and stores the final count in a variable |
For the people who want to save these SSIS Transformations, you can save the below-shown image
