SQL Server Integration Services (SSIS) - Data Flow Transformations in SSIS


In this article we are going to see what Data Flow Transformations in SSIS are and the list of controls that are provided in the data flow transformations followed by a series about each control including their usage.

To follow my series of articles on SSIS packages, please check my profile.


Follow steps 1 to 3 on my first article to open the BIDS project and select the right project to work on integration services project. Once the project is created, we will see on what exactly the data flow transformations are and where to locate the controls under this transformation and the usage of those transformations. After opening a new project just move to the Dataflow tab in the designer window and you can see the list of Data Transformations as shown in the below image.


Data flow transformations are helpful to do any type of manipulations across the data which are to be transferred and used in the package.

There are 28 data flow transformation controls and the list of them are as below with a small description of what each control is used for.

S No Transformation Description
1 Aggregate Aggregates and groups values
2 Audit Adds audit information
3 Character Map Applies string operations to character data
4 Conditional Split Evaluates and splits up rows
5 Copy Column Copies a column
6 Data Conversion Converts data to a different data type
7 Data Mining Query Runs a data mining query
8 Derived Column Calculates a new column from existing data
9 Export Column Exports data from a column to a file
10 Fuzzy Grouping Groups rows that contain similar values
11 Fuzzy Lookup Looks up values using fuzzy matching
12 Import Column Imports data from a file to a column
13 Lookup Looks up values in a dataset
14 Merge Merges two sorted datasets
15 Merge Join Merges data from two datasets by using a join
16 Multicast Creates copies of a dataset
17 OLE DB Command Executes a SQL command on each row in a dataset
18 Percentage Sampling Extracts a subset of rows from a dataset
19 Pivot Builds a pivot table from a dataset
20 Row Count Counts the rows of a dataset
21 Row Sampling Extracts a sample of rows from a dataset
22 Script Component Executes a custom script
23 Slowly Changing Dimension Updates a slowly changing dimension in a cube
24 Sort Sorts data
25 Term Extraction Extracts data from a column
26 Term Lookup
Looks up the frequency of a term in a column
27 Union All Merges multiple datasets
28 Unpivot Normalizes a pivot table

In our upcoming articles we are going to explore each of the major controls including the purpose of each.


So in this article we have seen what exactly Data flow transformations are and the list of available controls to perform these transformations.

Similar Articles