SSIS 2012 - Data Flow Transformations Handout

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