Using variable in Condition Split

SQL Server offers conditional split transform in SSIS which can be used to segregate data to different outputs based on the conditions specified. Here I demonstrate this by using variable to define the condition and use this variable in conditional split transform to separate the data to different destinations.

I am using SalesOrder database for this demo. I am going to separate the products data based on the discount offered into three different outputs. The first output will have the products with maximum discount, the second output will have the discounted products other than maximum discount and third output will have products with no discount. To achieve this, get the data from the source, transform that data and then move the data to three different flat file.

Let’s begin to design the SSSI package.

Create a variable to hold the maximum discount value. To create a variable, click on the control flow and then go to SSIS -> variables to open the variable window.

Give variable a name and specify the data type.

Now, to get the maximum discount value, drag and drop Execute SQL Task to the Control Flow tab of the SSIS designer window. Double click the ‘Execute SQL Task’ and on the ‘General’ window provide the connection details, set the ‘Result Set’ to Single row and write the SQL code in the SQL Statement section. The below SQL code gets the maximum discount value.

SELECT MAX(Discount) AS MAXDiscount FROM [Order Details]

Below is the general section of the Execute SQL Task.

In the ‘Result Set’, select the variable from the drop down list. This is the variable created in the beginning. Optionally provide a name in the ‘Result Name’. Click on OK to close the window. This will assign the max discount value to the variable.

Now we need to get the ‘Data Flow Task’ to get the data from the source and move the data to flat file. Drag and drop the ‘Data flow Task’ to the control flow tab in the SSIS designer area. Connect the ‘Execute SQL Task’ and the ‘Data flow Task’ as shown below.

Next in the data flow Task, we will define the data source, destination and condition split transform. Double click the ‘Data Flow Task’ to go to the data flow tab in the SSIS designer. Drag and drop the OLE DB Source to the Data flow tab. Double click the OLE DB source and provide the connection details in the connection manager. Select the ‘SQL command’ in the Data access mode and write the SQL code in the SQL command text window as shown below.

The below code returns the product name and discount records.

SELECT DISTINCT (PR.ProductName), OD.Discount FROM [Order Details] OD
INNER JOIN Products PR
ON OD.ProductID = PR.ProductID

Go to the Columns in the OLE DB source editor and ensure the required columns are selected as shown below.

Click on the error output in OLE DB source editor. We are not going to change anything here. Click on ‘OK’ to close the OLE DB source editor. Rename the OLE DB source to SQL Server.

Next we will define the condition to separate the products data. To do this we will drag and drop the condition split transform to the Data flow tab. Connect the OLE DB source to condition split transform. Right click the condition split transform and click on edit to open the condition split transformation editor. Expand the Columns and Variable and Parameters folder.

Here we will define the condition – ‘Discount == @[User::MaxDiscount]’ to split the products data of maximum discount.

Enter the ‘MaxDiscount’ in the Output Name row. Drag and drop the Discount in the condition row and specify the equivalent operator ‘==’. Next drag and drop the user variable in Condition row as shown below.

Next define the condition – ‘Discount > 0 && Discount < @[User::MaxDiscount]’ to split the data for the discounted products other than maximum discount.

Leave the default output name at the bottom of the editor and click on ‘OK’. If the above two conditions are not met than the transform uses the default condition to split the data. We will look at it bit later.

Next we will add destination to the below Data flow task.

Drag and drop the flat file destination to the data flow task for the products data with maximum discount. Connect the data flow path of the condition split transform to the flat file destination. While connecting, it will ask the output option to choose as below. Select ‘MaxDiscount’ and click OK.

Provide the connection manager details, file name and check the option ‘column names in the first data row’ as shown below and click on OK.

Next map the columns as shown below and click on OK.

Drag and drop the Flat file destinations for the discounted products data other than maximum discount and for the products data with no discount. Select the output options ‘Discount’ and the ‘conditional split default output’ accordingly while connecting the data flow path. Here the default output contains the data that doesn’t not meet the explicit condition specified in the condition split transform.The final data flow task is shown below.

Execute the package. The data is written to the destinations based on the condition specified in the condition split. The output below shows the number of rows written to ‘MaxDiscount’, ‘Discount’ and ‘NoDiscount’.