Introduction
In this article I will list and define the various types of Transformations in SSIS-2012 Version.
Various Types of Transformations in SSIS-2012
In SSIS-2012 there are nearly 30 types of transformations.
Depending on the functionality transformations are divided into the following five groups:
- Business Intelligence Transformations
- Split And Join Transformations
- Row Transformations
- Row-Set Transformations
- Other Transformations.
Business Intelligence Transformations
This transformation is classified into the following six types:
- Fuzzy Group Transformation: The Fuzzy Group Transformation is used do data cleansing by finding rows that are likely duplicates and reduce the number of duplicates with in a dataset based on the Matching Decision. This Transformation will accept only string data types while reducing the amount of duplicate data.
- Fuzzy Lookup Transformation: The Fuzzy Lookup Transformation is used to return close matches of reference data for the incoming data stream. It can be matches and standardizes the data based on fuzzy logic.
- Term Extraction Transformation: Extracts terms (nouns and noun phrases) from the input text into the transformation output column.
- Term Lookup Transformation: Extracts terms from the input column with TEXT data type and match them with the same or similar terms found in the lookup table. Each term found in the lookup table is scanned for in the input column. If the term is found then the transformation returns the value as well as the number of times it occurs in the row. You can configure this transformation to do a case-sensitive search.
- Data Mining Query Transformation: Queries a data mining model. Includes a query builder to assist you with the development of Data Mining eXpressions (DMX) prediction queries.
- Data Cleansing Transformation: This transformation is used to do automating data cleansing and monitoring the overall status of the data cleansing process.
Split and Join Transformations
This transformation is classified into the following seven types:
- Cache Transformation: This transformation is used to store the data as a file or in memory for use in a look up transformation
- Conditional Split: This transformation is used to accepts an input and determine which destination to pipe the data into based on the result of an expression. It redirect rows of data that meet specific conditions to different outputs
- Look-Up Transformation: This Look Up Transformation is used to join the input data set to the reference table, view or row set created by a SQL statement to lookup corresponding values. If some rows in the input data do not have corresponding rows in the lookup table then you must redirect such rows to a different output.
- Merge Transformation: This transformation is used to merge two sorted inputs into a single output based on the values of the key columns in each data set. Merged columns must have either identical or compatible data types
- Merge-Join Transformation: This transformation is used to merge two datasets into a single dataset using a JOIN function.
- Multicast Transformation: This transformation is used to send a copy of the data to an additional path in the workflow. It duplicates the data in the dataflow that again we can send the data in parallel, or when we want to send the data to multiple destinations simultaneously.
- Union-All Transformation: Combines multiple inputs into a single output. Rows are sorted in the order they're added to the transformation. You can ignore some columns from each output, but each output column must be mapped to at least one input column.
Row Transformations
This transformation is classified into the following six types.
- Character Map Transformation: The Character Map transformation allows you to do character operations on string columns. It makes common string data changes for you.
- Copy Column Transformation: This transformation is used to add a copy of column to the transformation output. You can later transform the copy. Makes a copy of a single or multiple columns that will be further transformed by subsequent tasks in the package
- Data Conversion Transformation: This transformation is used to convert a column data type to a new (another) column data type.
- Derived Column Transformation: This transformation is used to apply expression to a data column and create a new derived column calculated from an expression.
- OLEDB Command Transformation: Runs a SQL command for each input data row. Normally your SQL statement will include a parameter (denoted by the question mark)
- Script Component Transformation: This transformation is used to do a custom transformation.It uses a script to transform the data and you can apply specialized business logic to your data flow.
Row Set Transformations
This transformation is classified into the following six types:
- Aggregate Transformation: This transformation is used to aggregates the data from transformation or source and it aggregates the values by group.
- Row Sampling Transformation: This transformation is used to capture a sampling of the data from the data flow by using a row count of the data flow's total rows. It Loads only a subset of your data, defined as the number of rows and it randomly selects the data and is delivered to somewhere.
- Percentage Sampling Transformation: Loads only a subset of your data, defined as the percentage of all rows in the data source. It randomly selects percentage of rows.
- Sort Transformation: This transformation is used to sort the data in the data flow by a given column and discard with duplicate values (optionally eliminating duplicates).
- Pivot Transformation: This transformation is used to pivot the data on a column into a more non-relational form. It converts rows into columns.
- UnPivot Transformation: This transformation is used to unpivot the data from a non-normalized format to a relational format.
Other Transformations
This transformation is classified into the following five types:
- Audit Transformation: This transformation is used to expose auditing information from the package to the data pipe, such as package execution and Execution Time.
- Row Count Transformation: This transformation is used to count the rows in the data flow and stores them as a variable.
- SCD Transformation: This transformation maintains the historical values of the dimension members when new members are introduced. It automatically generates transformations for TYPE1 and TYPE2 SCD's.
- Export Transformation: This transformation is used to export the column from the data flow to the system.
- Import Transformation: This transformation is used to read data from files and appends it to the data flow.
Finally we can see the List of SSIS-2012 Transformations alphabetically as below:
- SSIS Aggregate Transformation
- SSIS Audit Transformation
- SSIS Cache Transform Transformation
- SSIS Character Map Transformation
- SSIS Conditional Split Transformation
- SSIS Copy Column Transformation
- SSIS Data Conversion Transformation
- SSIS Data Mining Query Transformation
- SSIS Derived Column Transformation
- SSIS DQS Cleansing Transformation
- SSIS Export Column Transformation
- SSIS Fuzzy Grouping Transformation
- SSIS Fuzzy LookUp Transformation
- SSIS Import Column Transformation
- SSIS Look Up Transformation
- SSIS Merge Transformation
- SSIS Merge Join Transformation
- SSIS Multicast Transformation
- SSIS OLEDB Command Transformation
- SSIS Percentage Sampling Transformation
- SSIS PIVOT Transformation
- SSIS Row Count Transformation
- SSIS Row Sampling Transformation
- SSIS Script Component Transformation
- SSIS Slowly Changing Dimension Transformation
- SSIS Sort Transformation
- SSIS Term Extraction Transformation
- SSIS Term Look Up Transformation
- SSIS Union All Transformation
- SSIS UnPivot Transformation
Summary
In this article I try to explain the various types of SSIS-2012 Transformations. I hope after reading this article your SSIS-2012 Transformation concepts will be strong. I would like to have feedback from my readers. Please post your feedback, question, or comments about this article.