List down different transformations available in SSIS

 It is an object that generates, modifies, or passes data.

1.    AGGEGATE –

It applies an aggregate function to grouped records and produces new output records from aggregated results.     

 

2.    AUDIT –

The transformation adds the value of a system variable, such as machine name or execution instance GUID to a new output column.

 

3.    CHARACTER MAP –

This transformation makes string data changes such as changing data from lower case to upper case.

 

4.    CONDITIONAL SPLIT -    

It separate input rows into separate output data pipelines based on the Boolean expressions configured for each output

 

5.    COPY COLUMN

Add a copy of column to the t/r output we can later transform the copy keeping the original for auditing personal

 

6.    DATA CONVERSION

Converts a columns data type to another data type.

 

7.    DATA MINING QUERY

Perform a data mining query against analysis services.

 

8.    DERIVED COLUMN

Create a new derive column calculated from expression.

 

9.    EXPORT COLUMN

It allows you to export a column from the data flow to a file.

 

10. FUZZY GROUPING

Perform data cleansing by finding rows that are likely duplicates.

 

11. FUZZY LOOKUP

Matches and standardizes data based on fuzzy logic.

eg:-transform the name jon to john

 

12. IMPORT COLUMN

Reads the data from a file & adds it into a dataflow.

 

13. LOOKUP

Perform the lookup of data tab used later in a transform.

ex:-t/f to lookup a city based on zipcode.

§  getting a related value from a table using a key column value

§  update slowly changing dimension table

§  To check whether records already exist in the table.

 

14. MERGE

 

Merges two sorted data sets into a single data set into a single data flow.

 

15. Merge Join

Merges two data sets into a single dataset using a join junction.

 

16. MULTI CAST

Sends a copy of two data to an additional path in the workflow.

 

17. ROW COUNT

Stores the rows count from the data flow into a variable.

 

18. ROW SAMPLING

Captures the sample of data from the dataflow by using a row count of the total rows in dataflow.

 

19. ROW SAMPLING

Captures the sample of the data from the data flow by using a row count of the total rows in data flow.

 

20. UNION ALL

Merge multiple data sets into a single dataset.

 

21. PIVOT

Converts rows into columns

 

22. UNPIVOT

Converts columns into rows