In this step by step SSIS Tutorial I will show you how to use SSIS Conditional Split Transformation. For the purpose of this tutorial I will use SSIS 2012.
Other Versions of SQL Server:
- SQL Server 2012
- This blog post has been written using SQL Server 2012
- SQL Server 2008 / R2
- The examples we used should work in SQL Server 2008 and R2
- SQL Server 2005
- We suspect that this will work in SQL Server 2005 as well
In this tutorial I present Conditional Split Transformation using SSIS 2012. I will split data flow source into multiple data paths based on Gender. Conditional Split can be sometimes tricky so I will cover 3 examples and show you what you should double check when you use conditional split.
In my first example I will show you how to split input into two outputs based using two conditions. My source query currently have two columns: EmployeeNationalIDAlternateKey and Gender and nine records. I would like to split it by Gender into Male and Female data paths.
NOTE: SQL is below:
SELECT TOP 5 EmployeeNationalIDAlternateKey, Gender
FROM DimEmployee AS de
WHERE Gender = 'M'
SELECT TOP 4 EmployeeNationalIDAlternateKey, Gender
FROM DimEmployee AS de
WHERE Gender = 'F'
In Visual Studio I have got package with OLE DB Source and AdventureWorksDW2012.
I open OLE DB Source Editor showing the SQL code. I open OLE DB Source Editor showing the SQL code. It uses Employee table to show us EmployeeNationalIDAlternateKey and Gender.
I click Preview button to show Preview Query Result. I have 5 Male and 4 Female rows. I Click close and then Ok to close both windows.
I drag Conditional Split Transformation from Toolbox to Data Flow and create new Path between them. And double click Conditional Split.
We get window with: Order, Output Name and Condition.
Order:Means that Condition with order 1 is evaluated first and if it is TRUE than it directs the row into output with this condition if it is FALSE it goes to next condition.
Output Name: This is the name we want to get for the data path.
Condition: Our condition that should be return either true or false. If condition is true then row is directed into the specified Output Name. Note that condition result may sometimes be NULL and that will raise error. We will discuss it in later on.
Now I create the condition. First Condition is Gender equal 'M' (Male) and the Second on is Gender equal 'F' (Female). I Type Output Name for both of them. In this case Male condition is first one so it means that this condition will be evaluated first.
I added two derived columns and created new data paths. When I connect to Derived Column I get pop up where I select my Output which I set up in Conditional Split Transformation.
I will unable data viewer to preview the data and run the package.
Our Input i 9 rows and we have 5 rows with Gender Male from 'Male Output' and 4 rows with Gender Female from 'Female Output'
So my Conditional spilt works. See below screenshots with rows affected.
We will add new links here to cover remaining option of conditional split soon.
I hope that helps