SSIS – Conditional Split Transformation

In this article, I create a simple ETL package that extracts data from a table and transforms the data using conditional split transformations and finally loads the result into multiple flat file destination.

SSIS is an ETL tool (Extract, Transform and Load) which is used in Data warehousing applications.

Transformations are used in Data Flow Tasks, between data source and destination components, or other transformations.

Data flow sources - Data from different External data sources to the data flow.
Data flow transformations - Transformations can perform tasks such as updating, summarizing, cleaning, merging, scrubbing, aggregating and distributing data.
Data flow destinations - Destination writes the data from a data flow.

Conditional Split transformation will be used to split the data based on some condition and save the result set in different destinations.
Let's see the step by step process for creating a package.

Step 1

Start - Programs - Microsoft SQL Server 2008 - SQL Server Business Intelligence Development Studio

Step 2

It will open a Visual Studio IDE.
To startup a project File – New – Project -Integration Services Project

Step 3

A new SSIS package is created. We have to design our package here.
Our Aim is to create a data flow task for that Drag and drop the Data Flow Task from Control Flow Items

Step 4

Double click on Data Flow Task will redirect to Data Flow window. Here we can design our Data Flow.
We are going to extract a table from Sql Server Database for that I create one table in Sql Server.


create table Players(Name varchar(30),Team varchar(10))

Insert into Players values
('Sachin Tendulkar' ,'India'),('Muttiah Muralitharan' ,'SriLanka'),
('Ricky Ponting' ,'Australia'),('Mahendra Singh Dhoni' ,'India'),
('Virat Kohli' ,'India'),('Sanath Jayasuriya' ,'SriLanka'),
('Kumar Sangakkara' ,'SriLanka'),('Brett Lee' ,'Australia'),
('Glenn McGrath' ,'Australia'),('Rahul Dravid' ,'India'),
('Virender Sehwag' ,'India'),('Mahela Jayawardene' ,'SriLanka'),
('Ravichandran Ashwin' ,'India'),('Lasith Malinga' ,'SriLanka'),
('Michael Hussey' ,'Australia')

select * from Players

To extract table from SQL Server drag and drop a OLE DB source from Data Flow Source toolbox.

Step 5

To provide connection for that source Right click on the connection Manager window and click on New OLE DB Connection. It will open a window like this.

Click New and create Data connections by providing Server Name, User Name, Password and Database Name.

Step 6

By double click on the OLE DB Source configure our database connection. Choose the table which we created on the database.

Step 7

Drag and drop Conditional Split flow item from toolbox and connect it to the OLE DB Source

Step 8

Double click on Conditional Split to apply conditions. Give the conditions inside double quotes.

Step 9

Drag and drop Flat File Destination from toolbox. Connect the Conditional Split output to the Flat File by choosing the output.

Step 10

To configure the Flat File double click on Flat File Destination a Flat File Destination Editor window will open. Click NEW to choose Flat File format.

Again Flat File Connection Manager Editor window will open. Select the location for your Flat File and check the Mappings.

For each Flat File Destination do the same.

Our final Data Flow Design will look like this

Step 11

Build the Package and Execute using F5.
We can find our Flat File output at specified location.