Conditional Split in SSIS

Often we are required to split the file based on various conditions and then either perform several actions to it or send the file to different departments. In this article, we will assume a real world scenario where in a company say ABC Inc. has different departments. The company has HR database where information of all the employees are stored. As an SSIS developer, we are required to collect the Employee details and send to employee's department when his department is "Manufacturing" and his hire date is before Jan 1st 2000. We will be using AdventureWorks DB for this purpose.

So, let's open up our BIDS àNew Projectà Choose Integration Services Project

We are naming our project as "ConditionalSplit". Below is how Control flow would look like before any configuration. In this Data Flow will fetch the data from source, split it on two different conditions and store it in excel file format and flat file format. Send Mail Task will then send the excel attachment to the given email address. If there is any failure in data flow task operator will be notified.

We will first configure Data Flow task to allow conditional splitting. So we double click data flow task and below is how the flow will look in data flow:

We will use ADO NET Source to connect to AdventureWorks Database. I have already shown as how we can connect to ADO.NET in my article here. In Data Access mode we will select SQL Command and will put below query

SELECT e.[EmployeeID],pa.AddressLine1, pa.City, hd.GroupName, e.HireDate

FROM [AdventureWorks].[HumanResources].[Employee] e

left outer join HumanResources.EmployeeAddress ea

ON e.EmployeeID=ea.EmployeeID

left outer join

Person.Address pa

ON ea.AddressID=pa.AddressID

left outer join

HumanResources.EmployeeDepartmentHistory h

on e.EmployeeID=h.EmployeeID

left outer join

HumanResources.Department hd

on h.DepartmentID=hd.DepartmentID


We will now configure Conditional Split. For this we will right click on conditional split and click Edit.

We will drag the column name shown on above left pane to the condition column below. Thereafter, we can apply the condition as shown below. We also might want to rename output names to something meaningful. The conditions used below which signifies we will be splitting the received data based on below 2 condition

  1.     Before_2000_Manufacturing        DATEPART("yyyy",HireDate) < 2000 && GroupName == "Manufacturing"
  2.     After_2000_Manufacturing        DATEPART("yyyy",HireDate) >= 2000 && GroupName == "Manufacturing"

Here we will be sending all the data that qualifies the condition named "Before_2000_Manufacturing" to Excel Destination and will send "After_2000_Manufacturing" to flat file destination. I have already shown how we can connect to Excel Destination in my previous article that can be found here. We will see how we can direct "After_2000_Manufacturing" condition to flat file.

Click Edit and dialog box will open click New Connection and choose the file format as Delimited, a new dialog box will open and in this I am keeping connection manager name as default, but you can give more appropriate name. Click on Browse->Select the file name if you already have it or else you can create here as well. I am naming my file as HireAfter_2000_Manufacturing. In my e.g. I am putting the Text Qualifier as | and checking for Column names in first data row as shown below. According to your requirements you can set other parameters as well.

In the Advanced Option you can change the DataType to whatever your requirement is. In this we are changing the EmployeeID column to [DT_STR].

Note: you can also take help of Suggest Types but use this at your own risk as sometimes it may not be very accurate as it suggests datatype by seeing small subset of data.

This covers our Data Flow task in control Flow. For configuring Send Mail Task, refer to my article here.

We are now ready to execute our package and if everything is right we will see below results

Tags: , , , ,