Conditional Split Transformation Task in SSIS 2008

Well, divide data in different destination based on certain condition, is very important task in any ETL tool. SSIS 2008 is really very rich for this kind of task. One of the tool help us to do it very quickly is “Conditional Split Transformation Task” in SSIS 2008.

Let us see one very small example which will give you demonstration of how to use “Conditional Split Transformation Task” in SSIS 2008.

We will first create one table, named “SampleReceive”, generally in environmental laboratory there are few different kind of department in which sample comes for different kind of analysis. I will use three department. 1.) VOA 2.)Semi-VOA 3.) Pesticide. We will make an entry in these three departments in “SampleReceive” table and later on, we will split data in three different table respective to particular department.

So, before we head towards SSIS project, let us first create necessary table in SQL Server. Below given is T-SQL script for the same.

create table SampleReceive

(

      Department varchar(10),

      NumberofSample int,

      DateReceived datetime,

      ClientID int

)

INSERT INTO SampleReceive

SELECT 'VOA',10,GETDATE(),1 UNION ALL

SELECT 'SEMI-VOA',7,GETDATE(),2 UNION ALL

SELECT 'Pesticide',16,GETDATE(),1 UNION ALL

SELECT 'VOA',5,GETDATE(),3 UNION ALL

SELECT 'SEMI-VOA',3,GETDATE(),1

GO

Create Table SampleReceiveVOA

(

      Department varchar(10),

      NumberofSample int,

      DateReceived datetime,

      ClientID int

)

Create Table SampleReceiveSemiVoa

(

      Department varchar(10),

      NumberofSample int,

      DateReceived datetime,

      ClientID int

)

Create Table SampleReceivePest

(

      Department varchar(10),

      NumberofSample int,

      DateReceived datetime,

      ClientID int

)

GO

Now, let us open new project in SSIS and start making a SSIS package.  As soon as you create new project, have “Data Flow Task” on your screen and double click on it to configure. Look at the below screen shot.

Once you double click on “Data Flow Task” you will be forwarded to the “Data Flow” tab besides “Control Flow” tab.  Now, drag “Ado Net  Source” task to your screen and double click on it to configure it. Give your server credential in configuration editor and select the source table which is “SampleReceive” in our case. For more details, have a look at below given screen shot.

Once you are done with configuration of “Ado Net Source”, drag “Conditional Split Transformation” task from tool box to your work area and drag green arrow coming from “Ado Net Source” to your “Conditional Split Transformation” to connect “Conditional Split Transformation” with “Ado Net Source”.

Now, this is the time to configure “Conditional Split Transformation” so double click on it. In “Conditional Split configuration editor” You have to set three conditions for all three different departments and give it a appropriate “Output name”. have a look at image below for more clarification.

Now, we are all set to split data into three different table from our “SampleReceive” table. Have three “Ado Net Destination”  on the screen and rename it to “VOA”, “Semi-Voa”, “Pesticide” and connect all three “Ado Net Destination” from “Conditional Split Transformation” task.  When you will try to connect that green arrow from “Conditional Split Transformation” to “VOA” Ado Net destination, it will ask you for select condition, you have to select “Voa” condition which we have already defined in “Conditional Split Transformation” task. Look at screen capture below for more information.

Once you connect it (all three destinations), You have to configure all three “Ado Net Destination” . I will show you how to do it for first one, for remaining two; you can follow the same procedure.

Double click on “Ado Net Destination” task named “VOA” and configure it with your SQL server details and table named “SampleReceiveVOA” as shown in below image.

Once you will configure “VOA” table, configure all remaining two “Ado Net Destination” for respected table. Finally your diagram will looks like below image.

Now, you are ready to run, hit F5 to run your package and check whether you have really received data in all three blank table or not by running following TSQL commands in your SQL Server database from SSMS.

select * from SampleReceiveVOA

select * from SampleReceiveSemiVoa

select * from SampleReceivePest

Reference: Ritesh Shah
http://www.sqlhub.com
Note: Microsoft Books online is a default reference of all articles but examples and explanations prepared by Ritesh Shah, founder of http://www.SQLHub.com