THE SQL Server Blog Spot on the Web

Welcome to SQLblog.com - The SQL Server blog spot on the web Sign in | |
in Search

Andy Leonard

Andy Leonard is CSO of Linchpin People and SQLPeople, an SSIS Trainer, Consultant, and developer; a Business Intelligence Markup Language (Biml) developer; SQL Server database and data warehouse developer, community mentor, engineer, and farmer. He is a co-author of SQL Server 2012 Integration Services Design Patterns. His background includes web application architecture and development, VB, and ASP. Andy loves the SQL Server Community!
Note: Comments are moderated. Spam shall not pass! </GandalfVoice>

SSIS Expression Language and the Conditional Split Transformation

Introduction

This post is part of a series titled An Introduction to the SSIS Expression Language.

In this post, I demonstrate the SSIS Expression Language and the Conditional Split Transformation.

To build the demo project described in this article you will need SSIS 2005 and the AdventureWorks sample database (which can be downloaded at http://www.codeplex.com/MSFTDBProdSamples/Release/ProjectReleases.aspx?ReleaseId=4004).

Build The Demo Project

If you have not already done so, create an SSIS project named ExpressionLanguageDemo.

Once the project is created, open Solution Explorer and rename Package.dtsx - change the name to ConditionalSplit.dtsx. If you've already created the project, right-click the SSIS Packages folder in Solution Explorer and click New Package - and rename that package to ConditionalSplit.dtsx. When prompted, rename the package object as well. (I think you should always do this - I always answer this message box by clicking the Yes button.)

Drag a Data Flow Task onto the Control Flow and click the Data Flow tab to edit. Drag an OLE DB Source onto the canvas. Double-click the OLE DB Source to open the editor. As in the post entitled SSIS Expression Language and Variables, click the New button to create a new Connection Manager to the AdventureWorks database.

Set the Data Access Mode to SQL Command and enter the following T-SQL statement into the SQL Command Text textbox:

Select
  ContactID
 ,Title
 ,FirstName
 ,MiddleName
 ,LastName
 ,EmailAddress
From Person.Contact

Click OK to close the editor.

On One Condition...

Drag a Conditional Split transformation onto the Data Flow canvas and connect the OLE DB Source Adapter to it:

Double-click the Conditional Split to open the editor. Expand the NULL Functions folder in the Operations list (upper right). Drag an ISNULL function into the Condition column of the first row in the Conditions grid. Expand the Columns folder in the Values list (upper left). Click and drag the Title column from the list onto the <<expression>> placeholder in the ISNULL expression:

The default name for a Condition is "Case n" where n is the row number of the Condition in the Condition grid.

Next, drag and drop the ContactID column into the second row's Condition column. Complete the expression so that it reads: ContactID <= 5000. Rename the Outputs to NullTitles and SmallContactIDs respectively:

What we've done here is define a couple of outputs. One of the outputs will contain rows where the Title is NULL. The other will contain - this is important, pay attention - rows where the Title is not NULL and the ContactID is less than or equal to 5000. Why is this? It's because rows with NULL Titles are redirected to the NullTitles output first. The ContactID value of these rows is never evaluated to see if it's less than or equal to 5000. If neither condition applies to a row, that row is sent to the Conditional Split Default Output. This operates a lot like a Switch statement in C# or a Select Case statement in VB, with the Default Output acting like the Else branch. You can adjust the order of condition evaluation using the spinner buttons on the right:

Click OK to close the Conditional Split editor.

Terminate It! 

Drag a Multicast transformation onto the Data Flow task surface. We'll use the Multicast to terminate a Data Flow Path. Drag a Data Flow Path from the Conditional Split transformation to the Multicast. When you do this, you'll be prompted to select an output from the Conditional Split to connect to the Multicast input:

After selecting an Output, click Ok to close:

The title of the output you selected appears in the label.

Conclusion

 Expressions are used to branch data row flow inside the Data Flow Task with the Conditional Split transformation.

:{> Andy 

Published Friday, February 20, 2009 12:04 PM by andyleonard

Comment Notification

If you would like to receive an email when updates are made to this post, please register here

Subscribe to this post's comments using RSS

Comments

 

theva said:

simple and easy to understand. Great work Andy

February 4, 2010 12:18 AM
 

julia said:

Thanks Andy, very clear and easy to follow.

June 6, 2010 5:10 AM
 

Nitin Joshi said:

Clean demonstration..........!

July 21, 2010 7:57 AM
 

Angela said:

Thanks!  Your sample solved my problem!  

January 27, 2011 11:14 AM
 

Bala said:

Really easy understanding...Andy i need help in Deployment of packages in other machines or remote servers, can you share the link

April 19, 2011 6:18 AM
 

Royan said:

DO you know how to terminate a SQL task based on some condition?

September 16, 2011 8:53 AM
 

andyleonard said:

It depends on what you mean by "terminate". Email me with details. There's a link in the upper right corner. :{>

September 16, 2011 9:19 AM
 

Tahir said:

Very nice man u rock

October 8, 2011 3:52 AM
 

jsa said:

thanks reallyusefull....

May 4, 2012 1:27 AM
 

swarupa said:

very useful , thanks a lot

August 8, 2012 7:23 AM
 

melissa said:

Andy,

Lets say you wanted to send these outputs to an actual table but needed to do a data conversion on non null values and the ones with null values you want to go directly to table. How would you go about this. I know the path for the data conversion but I don't know how I get the rows that had null to this table. Your help is much appreciated!

July 26, 2013 11:31 AM

Leave a Comment

(required) 
(required) 
Submit

This Blog

Syndication

My Company


Other Blog

Check out my personal blog...
http://andyleonard.me

Contact Me

Twitter: @AndyLeonard
Email: andy.leonard@gmail.com

Powered by Community Server (Commercial Edition), by Telligent Systems
  Privacy Statement