SSIS Transformations

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.

SSIS Transformations with Examples

In this article, we will show you the list of available SSIS transformations and explains their working functionality.

The following are a list of transformations available in SQL Server Integration Services or SSIS. I suggest you click on the URL (red color links) to navigate to the required SSIS 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 TransformationsDescription
Slowly Changing DimensionThis transformation coordinates the inserting and updating records in data warehouse dimension tables. The SSIS supports Type 0, Type 1, and SCD Type 2
Fuzzy Grouping TransformationIt is used to replace the wrongly typed words with correct words, by grouping technique.
Fuzzy Lookup TransformationThis 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 TransformationIt 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 TransformationIt 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 TransformationThis SSIS transformation helps us to run data mining queries.
DQS Cleansing TransformationIt 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 TransformationsDescription
Character Map TransformationIt is useful to transform input characters to uppercase, lowercase, etc.
Copy Column TransformationIt simply duplicates the source columns. Just like copying the column data, and pasting it in a new column.
Data Conversion TransformationThis transformation is used to convert the data from one data type to another data type. It is similar to CAST or CONVERT
Derived Column TransformationIt helps us to deal with SSIS expressions
Export Column TransformationIt is handy to export images, binary files, media, or any large documents from SQL Server to file system.
Import Column TransformationThis 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 ComponentIt 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 TransformationIt 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 TransformationsDescription
Aggregate TransformationThis SSIS transformation performs the aggregations such as SUM, MIN, AVG, etc
Sort TransformationIt 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 TransformationThis 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 TransformationIt provides an option to specify the number of rows you want to retrieve from the data source.
Pivot TransformationIt 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 TransformationIt 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 TransformationsDescription
Conditional Split TransformationIt 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 TransformationThis SSIS transformation sends input data to multiple destination paths without any modification.
Union All TransformationIt 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 TransformationThe 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 TransformationThis transformation is used to compare source data with existing data present in the reference table (or Lookup Table) to find matching ones
Cache TransformationThis 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 TransformationsDescription
Audit TransformationIt is used to find the auditing information such as Machine name, package name, id, date, etc
Row Count TransformationThis transformation counts the rows passed through a data flow, and stores the final count in a variable

Comments are closed.