Conditional Split in SQL Services Integration Services (SSIS)

Conditional Split is an important feature of SQL Services Integration Services (SSIS). In this article, we will learn how to perform conditional split. Now, first, let’s understand what conditional split is.

Conditional Split transformation is a Data Flow Component that splits the data based on specific conditions. It’s a powerful tool that divides an input stream into multiple output streams, which helps businesses manage and process the data.

Now, let’s understand more with an example. Let’s create an SSIS package named “Conditional Split Task” and then we will create a data flow task.

Within the data flow task, we will add OLE DB Source and Conditional Split transformation from the SSIS toolbox. In the below screenshot, you can see how it looks.

Here, we will use the AdventureWorksDW2019 database to perform a conditional split that is already loaded in the SQL server.

Now, we will establish an OLE DB Source connection and use the below query in the SQL Command section.

SELECT *,
       NTILE(4) OVER (ORDER BY [CustomerKey]) AS NTileOutput
FROM [AdventureWorksDW2019].[dbo].[DimCustomer]
ORDER BY [CustomerKey];

In the above query, the NTile(N) Function splits the DimCustomer table into four random groups. The connection has been established, and we can see it in the screenshot below.

We use SSIS expressions to specify which rows are routed where and use a conditional split transformation to push this randomly grouped data into four different UNION ALL. Now, add UNION ALL in the SSIS package from Toolbox.

Now let’s go to Conditional Split Transformation Editor and apply a condition like [NTileOutput] ==1 for Group 1 and for other groups, too, and hit OK.

Now, we will establish the connection between Conditional Split and all 4 UNION ALL. And Enable Data Viewer for all 4 UNION ALL. We will also add one more UNION ALL for Default Output, and by default, it will take Conditional Split Default Output. See the screenshot below to understand more.

Now, let’s execute a Conditional Split Task and see the result.

As you can see, the table was split into four random groups (Case 1, Case 2, Case 3, and Case 4) based on a given condition.

Summary

There are several benefits of creating a Conditional Split Task in SSIS, for example.

• We can use logical operators (AND, OR, NOT) to create complex conditions and achieve the desired result.
• We can define multiple conditions and route the data to different outputs based on each defined condition.

Hope you liked the article, please share the feedback/suggestions in the comments section below.