Aim : This article is written keeping in mind the intention of visitors who wants to learn in depth about Conditional Split transformation in SSIS.
- Definition – SSIS Conditional Split Transformation as the name suggests split the data based on conditions. In other words, it can route the source data into different places/destinations based on the conditions we mention while configuration of this component. You can think of CASE statement in programming.
- Where to Find – You can find Conditional slit transformation in Data flow level for the implementation.
- Where to Use – It can be used in scenarios where you need to split the source data into different output paths based on your business logic.
- Example – Suppose, you have one table named ABC_Inc with columns like Emp_Id, Emp_Name, Dept_Id, Dept_Name. Now, if you need to split this table based on Dept_Id then, with Conditional split you can achieve this. So, in the destination, you can get output files like IT department employee in one file, HR department employee in second file and so on.
I guess this much theory is sufficient for high level understanding about Conditional split transformation in SSIS. Let us look into one very good scenario to learn the functionality
Conditional Split transformation in SSIS
STEP 1 : Create one table, say Test and Insert values into Test.
--Create table Test
CREATE table Test
--Inserting into table Test
Insert into Test values(1,'Chander',2000), (2,'Avinash',1500),
STEP 2 : Create SSIS package and Configure source.
- Open SSDT/BIDS (SQL Server Data Tool) and create one SSIS project.
- Within that project, create one SSIS package and name it as “Condition Split.dtsx”.
- Drag and drop Data flow task into control flow pane.
- Double click on Data flow task. Now, drag and drop “OLEDB source” from toolbox.
- Now, create a connection to the source table to configure your OLEDB source.
NOTE : These are pretty simple steps and we assume you all have understanding about the above points.
STEP 3(a) : SSIS Conditional Split Transformation
- Drag and Drop Conditional Split transformation from the toolbox and create a connection with OLEDB source.
- Now, Double click on SSIS Conditional Split transformation to open Component editor for the configuration. If you have any doubt while doing this, just take a look at below screenshot.
STEP 3(b) : Configure SSIS Conditional Split Transformation
- Double click on Conditional split component.
- Before getting into the configuration, I would like to tell you the conditions on which we will split the data. I am going to split the data based on the column “Empid”.
- Here I’m taking two cases –
- Case1: If an “Empid <= 5” then, move that specific data into the first destination.
- Case2: If an “Empid >= 5 && Empid < 10” then, move those records into second destination.
- Default Case: If we have any incoming data that doesn’t meet above two cases, it will directly route to “Conditional Split default output”.
- Now, let us concentrate on configuring SSIS Conditional split transformation to get the output as per our business logic i.e. to split the data according to the conditions (cases) mentioned above.
- Let us write expressions in the editor to get intended result. For reference, see below screenshot while writing the expressions. I’m using “Empid” column and I divided the conditional split transformation editor into different sections to create more interaction with it.
- Once, you are done with Configuring Conditional Split transformation, Click OK button.
STEP 4 : Explanation of Conditional Split Transformation sections
- Section 1 : In this section, we have 2 folders – Variables and Columns. Variables contains the list of both System defined and User defined (if we have created any externally). Columns contain the information about the input columns coming from the source Table/Query.
- Section 2 : This section contains different type of functions like Mathematical, String, Date/Time and few more. We can use these functions to write an expression as per our requirement.
- Section 3 : This is the main section where we can write our conditions in the form of expressions. Note – Expressions are always Case sensitive. For example – If we have a column name, say “EMPID” and we used “Empid” in our expression then, it will throw an error. This is because the original column name is in capital letters and we need to use exactly the same for writing an expression. Best practice to follow is to drag and drop column names from the available list to build any expression in SSIS.
- Section 4 : In this section, we get benefits in Configuring error outputs. For example – If we have some data coming from the source and it contains some invalid records. Then, while running our SSIS package, it will throw an error and will get failed due to these invalid data records. Now, if we want to move our process smoothly forward and want to track those invalid records in some table/file then ,we can go for “Configure Error output” option.
STEP 5 : Working of Conditional Split Transformation in SSIS
- Now, Drag and drop 3 Derived column transformation to the Data flow pane and arrange them below Conditional Split component. Also, you can enable the data viewers between “Conditional Split” and “Derived column”if wish to see the results.
- Now, Drag and drop the connection arrow from “Conditional Split” to “1st Derived column”.
- While attempting the above step, you can notice a pop up window like below.
- Now, select Case 1 and press OK button for 1st Derived column.
- Again, pull the connection string from Conditional Split to 2nd Derived column to create a connection.
- This time Select Case 2 for 2nd Derived column.
- Repeat the same for the 3rd and final Derived column and choose “Conditional split default output”.
- Once you create 3 connections then enable the data viewers between connections (Conditional split- derived column).
- That’s all, just go ahead and run your SSIS package and enjoy your day.
- Please take a look at below screenshot for final results.
- In the above screenshot, we can clearly observe the results of 3 data viewers. Also, you see how the data gets split or divided into 3 outputs based on the conditions (cases) we provided as an expressions.
NOTE : Interview Question for SSIS Conditional Split Transformation
Question – CASE 1 was Empid <= 5 and CASE 2 was Empid >= 5 && Empid <10. If you observe both conditions closely together, Empid = 5 is valid for both the cases. Now, to which output Empid = 5 will go? Will it go to –
- 1st Derived column or
- 2nd Derived column or
- Both columns or
- Package will fail?
Explanation – It will go to 1st Derived column because if you look clearly into 4th picture (where 4 Sections were explained), there is a column, named as Order in Conditional Split Transformation editor. This tells the order by which the Conditions or Cases will execute. So, 1st order was for Case 1 i.e. Empid <= 5 and this is why Empid = 5 will go to 1st Derived column.
This is all we have for SSIS Conditional Split Transformation. We hope you enjoyed this post and if you loved our efforts or have any queries, kindly drop as a comment below.
Editor – Chander Sharma