SSIS Transformations

The SSIS transformations are the data flow components that are used to perform aggregations, sorting, merging, modifying, joining, cleansing, and distributing the data. Apart from these, there is an important, and powerful transformation 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

Following are a list of transformations available in SQL Server Integration Services. I suggest you to click on the URL (red color links) to navigate to required transformation and find the example with detailed explanation.

Business Intelligence Transformations in SSIS

The following list of 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 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 option, 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 is repeated in the source.
Data Mining Query Transformation	This 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 are 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 new column.
Data Conversion Transformation	This transformation is used to convert the data from one data type to another data type, which is similar to CAST or CONVERT
Derived Column Transformation	It helps us to deal with SSIS expressions
Export Column Transformation	It is very useful to export images, binary files, media or any sort of 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 Insert Data, Delete Data, and Update Data articles to understand the operations.

Rowset Transformations in SSIS

The below list of transformations will create new rowsets, and that rowsets may include aggregated data, Sorted data, or Pivoted and Unpivoted data.

SSIS Transformations	Description
Aggregate Transformation	This 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 T-SQL command ORDER BY statement.
Percentage Sampling Transformation	This 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 to 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 in SSIS

The following list of transformations are useful to distribute the source data to different outputs, and to join multiple outputs as single output.

SSIS Transformations	Description
Conditional Split Transformation	It is like IF, or CASE statement, which check the given condition and based on the result, the output will be sent to the appropriate destination path.
Multicast Transformation	This 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	It is used to merge two inputs such as tables or files, and produce 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 the source data with existing data present in the reference table (or Lookup Table) to find matching ones
Cache Transformation	This transformation write data from a connected data source to a Cache file (.caw extension) using Cache Connection Manager. It is very useful to perform Lookup on the data present in the cache file.

SSIS Auditing Transformations

The following SSIS transformations are used 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	It counts the rows that are passed through a data flow, and stores the final count in a variable

For the people who want to save it as an image

Thank You for Visiting Our Blog