Conditional Split Transformation

260 January 5, 2024
  • Definition:-  SSIS Conditional Split Transform one of the SQL Server Integration Services that can be added within a Data Flow Task. It is used to split the data into multiple destinations based on the specified conditions. In this one we want to split data based on the gender column in different tables.
    Procedure :

    • Creating package under Project “doc”.
    •  Under Solution Explorer we need to rename the package as required. i.e. Conditional split.dtsx.
    • Drag and Drop the Data Flow Task from the toolbox to the control flow region. Add a name to it of your choice.

  • Double-click on the Data Flow Task. Drag and drop the OLEDB source from the toolbox menu.

  • Double-click on the OLEDB source a menu will pop up. Enter the Database server name in the connection manager then select the table which contains the data.

  •  Next go the columns and check whether the displayed columns matched the tables in the selected SQL table. Then click OK.
  • Drag and drop the Conditional split from the toolbox menu.

  •  Double click on Conditional split. Give a output name as ‘male’ and write the Condition like ..
    Condition:- gender == “M”
  •  Add another output name as ‘female’ and write the Condition like..
    Condition:- gender == “F”

• Click on OK.
• Drag and drop the 2 OLEDB Destinations from Other Destinations in SSIS Toolbox and drag and drop on the workspace.

  • Map the Conditional split to Destination. Select the “Male” as shown the below. Click on Ok.

  • Drag and drop the OLEDB Destination1 from Other Destinations in SSIS Toolbox and drag and drop on the workspace. Map the Conditional split to Destination. Select the “Female” as shown the below. Click on Ok.

  • Double-click on the OLEDB destination a menu will pop up. Create at run time.
  • Click on mappings and check the all mappings are mapped or not. Click on OK.
  • We can now run the package with the run option present in the main menu. When we press the Start button then the package will start running, and you can view the status of your SSIS package.
  • Verify the output in the SQL Server. Go to SQL Server .