Learn more about SQL Server tools

mssqltips logo
 

Tutorials          DBA          Dev          BI          Career          Categories          Webcasts          Whitepapers          Today's Tip          Join

Tutorials      DBA      Dev      BI      Categories      Webcasts

DBA    Dev    BI    Categories

 

Intelligent Conditional Split in an SSIS Package


By:   |   Last Updated: 2010-10-14   |   Comments (5)   |   Related Tips: More > Integration Services Data Flow Transformations

Problem

Conditional Split is one of the most commonly used transforms in any SSIS Package development routine. The limitation with conditional split is that any record that satisfies the first condition from the conditions list is routed to its corresponding path exclusively. In a logical sense, a record might satisfy more than one condition and one might want the record to be routed to all paths for processing, but with the Conditional Split transform this is not possible. We need a more intelligent conditional split where we can selectively route the records to more than one output path. In this tip we will look at how to facilitate this intelligent conditional split.

Solution

We already know the problem in question and the solution that we are looking for can be facilitated using a synchronous script transform.

Say we have a list of employees having EmployeeID from 1 to 40. The requirement we have is that employees from 1 to 10 need to be processed by a particular logic, employees from 1 to 20 by another logic, 1 to 30 by another logic and 1 to 40 by another logic. If we have to do it using regular transforms, the easiest way I would think is to use a Multicast Transform and place 4 conditional split transforms. But this is not efficient as I would have to keep adding a conditional split transform for each range due to the limitation of this transform to direct rows to more than one output path.

Script transformation is one such transform which can provide intelligent split functionality. We will be using the Employee table from the AdventureWorks database, so I take it for granted that you have this available on your development machine.

To build the solution, follow the below steps:

1) Create a new SSIS project and add a new package.

2) Add a Data Flow task to the package and edit it.

3) Add an OLE DB Adapter to this data flow task and configure it to read the Employee table from the AdventureWorks database.

4) Add a Script transformation next to it. Edit the properties and on the Input Columns page select all of the columns.

5) On the Inputs and Outputs page, configure it to have four output paths named "1stBatch", "2ndBatch", "3rdBatch" and "4thBatch" as shown below.

6) Configure the SynchronousInputID property for each output path to point to the only available input path by selecting from the dropdown. Give a non-zero value to ExclusionGroup for all the output paths. For this example, assign a value of "1".

After you are done with the editing, your Inputs and Outputs page should look something like the below figure.

 how to facilitate this intelligent conditional split in any SSIS Package development routine

7) Once you make a Script output synchronous, the DirectRowTo[OutputPathName] method becomes available for each synchronous output path on the Row object as shown in the below code. To implement the code for our requirement, edit the Script and add the code as shown in the below figure.

implement the code for our requirement, edit the Script and add the code

8) Now add four multicast transforms to the code and join each corresponding output path from the script transform to its relevant multicast transform. After following these steps, your package should look like the below figure.

ssis rnd microsoft visual studio

Execute the package and you should be able to see the result as shown in the below figure, which is exactly what we wanted. Using just a single transform we are able to validate the logic and route the same record to multiple output paths based on the requirement.

using just a single transform we are able to validate the logic and route the same record to multiple output paths

Next Steps
  • Implement this solution in your existing and/or future packages.
  • Try changing the value of ExclusionGroup property back to "0" for any output path and see if the DirectRowTo[OutputPath] method still remains available.


Last Updated: 2010-10-14


next webcast button


next tip button



About the author
MSSQLTips author Siddharth Mehta Siddharth Mehta is an Associate Manager with Accenture in the Avanade Division focusing on Business Intelligence.

View all my tips




Post a comment or let the author know this tip helped.

All comments are reviewed, so stay on subject or we may delete your comment. Note: your email address is not published. Required fields are marked with an asterisk (*).

*Name    *Email    Email me updates 


Signup for our newsletter
 I agree by submitting my data to receive communications, account updates and/or special offers about SQL Server from MSSQLTips and/or its Sponsors. I have read the privacy statement and understand I may unsubscribe at any time.



    



Wednesday, September 04, 2013 - 2:31:02 AM - Oliver Back To Top

paul, this is just a simple requirement and probably fits to conditional split. but once you have a complicated validation as to where output to go to, this is one of the best!.


Friday, May 04, 2012 - 1:01:15 AM - marce Back To Top

MAN you have saved my life and my weekend!!!! Thanks so much


Friday, January 21, 2011 - 5:24:57 AM - Naveen kumar Back To Top

Good One. I came to know that even script task can be used as a multicast task


Tuesday, November 02, 2010 - 5:11:41 PM - Niall Back To Top

Brilliant!


Tuesday, November 02, 2010 - 7:04:52 AM - paul hutagalung Back To Top

Why not just multicast it and then make different conditional split? save time and effort.


Learn more about SQL Server tools