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

Introduction

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.

Steps:

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.

Conclusion

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