Data Flow Transformations
- Aggregate
- Conditional Split
- Data Conversion
- Derived Column
- Lookup
- Sort
- Merge
- Merge Join
- Multicast
- OLEDB Command
- Row Count
- Script Task
- Slowly Changing Dimension
- Union All
Aggregate Transformation
- Create a SQL Server
Integration Services project in SQL Server Data Tools. - Go to the SSIS packages folder in solution
explorer è Right click on the SSIS packages folderè Create
new packageè Save the package with Suitable name. - Open the package è Drag & drop the “Data Flow Task” from SSIS Toolbox.
- Open the Data Flow Task
- Create OLEDB source and
Destination in the DFT. - Drag & Drop Aggregate
Transformation in DFT from SSIS Toolbox. - Map the OLE DB Source to
Aggregate Transformation - Open the Aggregate
transformation and select the columns and their operations that we want to
do on the data. - We can perform GROUPBY,
COUNT, COUNT DISNTICT , SUM, MIN,MAX & AVG operations on the data - Map the output of
Aggregate transformation to OLEDB Destination. - Check the mapping in
OLEDB destination - Execute the package
Conditional Split
- Create a new package and
save it with suitable name - Open the package è Drag & drop the “Data Flow Task” from SSIS Toolbox.
- Open the Data Flow Task
- Create OLEDB source and Destination under DFT in the package.
- Drag and drop the
Conditional Split transformation from SSIS toolbox. And map the OLEDB
source to it. - Double click on the
Conditional Split transformation and set the condition for splitting the
data.
E.g EmployeeGender==‘Male’ - Map the data to two OLEDB
destinations one for matching the condition & another for not matching
the condition.
Data Conversion Transformation
- Create a new package and
save it with suitable name. - Open the package è Drag & drop the “Data Flow Task” from SSIS Toolbox.
- Open the Data Flow Task
- Create OLEDB source and
Destination under DFT in the
package. - Drag and drop the Data
Conversion Task in DFT from SSIS Toolbox and Map it to OLE DB source. - Open the Data conversion
Task and choose the column for which we want to change the data type. - Change the data type and
map the same column from Data
Conversion Task to output (OLEDB Destination).
Derived Column Transformation
- Create a new package and
save it with suitable name. - Open the package è Drag & drop the “Data Flow Task” from SSIS Toolbox.
- Open the Data Flow Task
- Create an OLEDB source
- Drag and drop the Derived
column transformation from SSIS Toolbox. - Map the OLEDB source to
Derived Column task. - Open the Derived Column
transformation and set the expression for any of the required input
column. - We can give the new name
to derived column. - Set the Data Viewer in
workflow between Source and Derived Column - Run the package to see
the result - We can map this newly
created derived column to OLE DB destination
Lookup Transformation
- Create a new package and
save it with suitable name. - Open the package è Drag & drop the “Data Flow Task” from SSIS Toolbox.
- Open the Data Flow Task
and create one OLE DB source and two OLEDB Destination. - If you want to connect to
a cache file, select a Cache connection manager. - Specify the table or view
that contains the reference dataset. - Generate a reference
dataset by specifying an SQL statement. - Specify joins between the
input and the reference dataset. - Add columns from the
reference dataset to the Lookup transformation output. - Configure the caching
options. - Map the Lookup match &
Lookup non match data to output destinations
Sort Transformation
- Create a new package and
save it with suitable name. - Open the package è Drag & drop the “Data Flow Task” from SSIS Toolbox.
- Open the Data Flow Task
- Create a OLE DB source
- Drag and drop Sort
transformation from SSIS toolbox - Map the OLEDB Source to
Sort transformation - Open the Sort
Transformation and select the column by which we want to sort the data in
ascending or descending order. - Map the output of the
Sort transformation to Derived column or OLEDB destination.
MERGE Transformation
- Create a new package and
save it with suitable name - Open the package è Drag & drop the “Data Flow Task” from SSIS Toolbox.
- Open the Data Flow Task
- Create two OLEDB data
sources and one OLEDB destination. - Use the Sort
transformation to sort the data before input to MERGE transformation - Double click on the MERGE
transformation and map the sort transformation’s outputs to MERGE
transformation. - Check the column mapping
- Map the MERGE Transformation
output to the OLEDB destination. - Execute the package
MERGE JOIN Transformation
- Create a new package and
save it with suitable name - Open the package è Drag & drop the “Data Flow Task” from SSIS Toolbox.
- Open the Data Flow Task
- Create two OLEDB data
sources and OLEDB destination. - Use the Sort
transformation to sort the data before input to MERGE JOIN transformation. - Specify the join e.g.
FULL, LEFT, or INNER join between Sort1 and Sort2 output. - Specify the common column
between sort1 & Sort2 output for joining the datasets. - Specify whether the
transformation handles null values as equal to other nulls. - Map the output of the
MERGE JOIN transformation to OLE DB destination.
OLE DB Command Transformation
- Create a new package and
save it with suitable name. - Open the package è Drag & drop the “Data Flow Task” from SSIS Toolbox.
- Open the Data Flow Task
- Create one OLEDB data
sources and OLEDB destination in DFT - Add OLE DB Command
transformation to DFTs. - Map the OLE DB source to
OLEDB command transformation. - Right click on the OLEDB
Command transformation and go to Advance Editor. - Provide the SQL statement
that the transformation runs for each row. - Specify the number of
seconds before the SQL statement times out. - Map the OLEDB Command
output to OLEDB destination.
Row Count Transformation
- Create a new package and
save it with suitable name - Open the package è Drag & drop the “Data Flow Task” from SSIS Toolbox.
- Open the Data Flow Task
- Create one OLEDB data
sources - Create a variable datatye
Int32. - Add RowCount
transformation to the package from SSIS Toolbox. - Double click on the Row
Count Transformation and map the output of transformation to variable. - We can utilize the
variable value for next steps of the package.
Script Transformation
- Create a new package and
save it with suitable name - Open the package è Drag & drop the “Data Flow Task” from SSIS Toolbox.
- Open the Data Flow Task
- Drag and drop the script
task from SSIS Toolbox. - Select the script
language either VB 2010 or Visual C# 2010 - Declare variables if you
need - Open the script editor
and write the code including variables if required - Save the code
- Execute the package
Slowly Changing Dimension
- Create a new package and
save it with suitable name - Open the package è Drag & drop the “Data Flow Task” from SSIS Toolbox.
- Open the Data Flow Task
- Create one OLEDB data
source - Drag and drop Slowly
Changing Dimension Transformation and map the OLE DB source to it. - Double click on the
transformation, it will start SCD wizard. - Select the key column for
the source table - Select the change type
attribute e.g. Changing attribute, Fixed attribute & Historical
attribute. - It will create two
destinations. One for simple insertion from source and another for
Changing attributes or SCD types. - Run the package.
Union All Transformation
- Create a new package and
save it with Suitable name - Open the package è Drag & drop the “Data Flow Task” from SSIS Toolbox.
- Open the Data Flow Task
- Create two OLEDB data
sources and one OLEDB destination. - Drag and drop Union All
transformation from SSIS ToolBox. - Map these two OLE DB data
sources to Union All transformation. - Open the Union All
transformation and do the column
mapping for input 1 and input 2. - Map the Output of
transformation to OLEDB destination. - Execute the package