This is part 30 of the series of article on SSIS and in this article we are going to see what is Data Flow Transformations in SSIS and the list of controls that are provided in the data flow transformations followed by the series on each of the control and the usage of the controls.
Introduction
In this article we are going to see on what Data Flow
Transformations in SSIS and the list of controls that are provided in the data
flow transformations followed by the series on each of the control and the
usage of the controls
To follow my series of articles on SSIS packages, please click here.
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 on what for the 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 see on each of the
major control and the purpose of them.
Conclusion
So in this article we have seen on what exactly Data flow
transformations are and the list of available controls to perform these
transformations.