Saturday, December 2, 2017

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


1 comment:

  1. I think you will not need a lookup transformation if you use SSIS Upsert. Although this is a third-party product but I still think it will be useful.

    SSIS Upsert

    ReplyDelete