Various Types of Transformations in SSIS-2012

In SSIS-2012 there are 30 types of transformations.Depending on the functionality transformations divide into five groups

Business Intelligence Transformations
Split And Join Transformations
Row Transformations
Row-Set Transformations
Other Transformations.

1. Business Intelligence Transformations

This transformation classified into six types

Fuzzy Group Transformation:

It uses data cleansing by finding rows. Those reduce the number of duplicates within a dataset based on the Matching Decision. This Transformation will accept only string data types. The amount of duplicate data is reducing.

Fuzzy Lookup Transformation:

The matches of reference data for the incoming data stream. It can match and standardizes the data based on fuzzy logic.

Term Extraction Transformation:

The input text into the transformation output column.

Term Lookup Transformation:

The input columns with TEXT data type same in the lookup table. Each term found in the lookup table scanned for in the input column. If the term transformation returns the value the number of times it occurs in the row. The transformation to do a case-sensitive search configure.

Data Mining Query Transformation:

Queries a data mining model. The development of Data Mining expressions prediction queries assist to the builder.

Data Cleansing Transformation:

It used to do automating data cleansing and monitoring. The status of the data cleansing process.

2. Split and Join Transformations

This transformation classified into seven types.

Cache Transformation:

It used to store the data as a file or in memory for use in a lookup transformation

Conditional Split:

This transformation uses to accept an input and determine. The destination to pipe data into based on the result of an expression. It redirects rows of data that meet specific conditions to different outputs

Look-Up Transformation:

This Look -Up Transformation used to join the input data. The reference table, view or row set created by a SQL statement to corresponding values. The input data do not have corresponding rows in the lookup table. Then redirect rows to a different output.

Merge Transformation:

The merge two sorted inputs into a single output. It 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:

merge two datasets into a single dataset using a JOIN function.

Multicast Transformation:

This used to send a copy of the data to an extra path in the workflow. It duplicates the data in the data flow can send the data in parallel. The send the data too many destinations.

Union-All Transformation:

Combines many inputs into a single output. Rows sort in the order they're added to the transformation. You can ignore some columns from each output, but each output column map to at least one input column.

3. Row Transformations

This transformation classified into six types

Character Map Transformation:

The character operations on string columns. It makes common string data changes for you.

Copy Column Transformation:

It used to add a copy of the column to the transformation. It output transform the copy. It makes a copy of a single or many columns. That will be further transformed by later tasks in the package

Data Conversion Transformation:

It used to convert a column data type to a new column data type.

Derived Column Transformation:

It used to apply the expression to a data column. The derived columns calculated from an expression create.

OLEDB Command Transformation:

Runs a SQL command for each input data row. The SQL statement will include a parameter.

Script Component Transformation:

It 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.

4. Row Set Transformations

This transformation classified into six types

Total Transformation:

data from transformation or source aggregates the values by the group.

Row Sampling Transformation:

It uses to capture a sampling of the data. The data flow using a row count of the data flow's total rows. It Loads only a subset of data, defined as the number of rows. It selects the data delivered to somewhere.

Percentage Sampling Transformation:

Loads only a subset of data. The percentage of rows in the data source. It selects the percentage of rows.

Sort Transformation:

It used to sort the data in the data flow by a column discard with duplicate values.

Pivot Transformation:

This use to pivot the data on a column into a more non-relational form. It converts rows into columns.

UnPivot Transformation:

the data from a non-normalized format to a relational format.

5. Other Transformations

This transformation classified into five types

Audit Transformation:

The expose auditing information from the package to the data pipe. Such as package execution and Execution Time.

Row Count Transformation:

count the rows in the data flow and stores them as a variable.

SCD Transformation:

the historical values of the dimension members introduced. It generates transformations for TYPE1 and TYPE2 SCD's.

Export Transformation:

The export column from the data flow to the system.

Import Transformation:

The read data from files and appends it to the data flow.

MSBI Tutorials Point