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.

A data flow component called conditional split transformation divides the data according to predetermined criteria. It is an effective tool that helps businesses handle and analyze data by splitting an input stream into several output streams.

Let's look at an example to better understand. Before creating a data flow task, let's establish an SSIS package called "Conditional Split Task."

OLE DB Source and Conditional Split transformations from the SSIS toolbox will be added to the data flow task. You can see how it appears in the screenshot below.

Here, we will use the Hostforlife 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 [Hostforlife].[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.

HostForLIFEASP.NET SQL Server 2022 Hosting