Friday, February 29, 2008

Expressions and Precedence Constraints

There was an interesting post on the SSIS Forums on MSDN earlier today. It went something (ok, exactly) like this:

I have a package that runs.  Inside the package I want to see if a specific table exists.  If it does, I go to the next step inside the package.  If it does not, I want to exit that package without errors or even warnings.
Any ideas?

What a great question! This is relatively common problem, but although the solution is quite simple, it can be far from obvious to people who have not spent a lot of time working with SSIS, and working with SSIS expressions and variables in particular.[1]

So how do we solve this problem? Simply! Here's how:

1) Create a variable to store a flag to track whether the table in which we're interested exists.

01 - Variable [2]

2) Use an Execute SQL task to query the SQL Server metadata to see if the table does exist, and store the result in this variable. If you're using SQL Server 2005 or later, this query will work. If not, you'll have to do a little additional research to find the right query for your environment.

          WHEN EXISTS (SELECT table_name
                         FROM INFORMATION_SCHEMA.TABLES
                        WHERE TABLE_NAME = 'Foo') THEN
       END AS TableExists;

Your Execute SQL task should look something like this, to store the return value from the query in the TableExists variable.

02 - General

03 - Result Set

3) Edit the properties of the precedence constraint that connects the Execute SQL task with the next task (the one that does the rest of the work) to base it on an expression that uses the variable (which has now been set by the Execute SQL task above) so that execution passes on to the next task only if the "table exists" flag is set.

04 - Expression

4) That's it! When you're done, your package should look something like this:

05 - Completed [3]

Now, when you execute your package, only the Execute SQL task should run, and then (because there is no valid precedence constraint for the package execution to follow) the package will exit without an error or warning, as desired.

06 - Does not exist

Conversely, if you happen to actually have a table named Foo in your database, execution will continue on, also as desired.

07 - Exists

Pretty cool and pretty simple, right? One variable + one expression == one simple approach to conditional package execution.

Expressions are probably the single-most powerful and important tool in all of SSIS. Although they are not the only tool, and although they are not the right solution to every problem, they apply to almost every situation where packages must be made dynamic.

This technique can also be used in scores of different scenarios, not just conditionally exiting package execution. I'll post more on this topic some day, but for now this will have to do. Not only do I have more writing to do, I also need to sleep at some point this week. Really...


[1] Sound familiar?

[2] Please note that I'm using a String variable and not a Boolean variable simply because it's late and I can't remember how to map an SSIS boolean to T-SQL. I know you can do it, but I'll leave that one as an exercise for the reader this time...

[3] I've set the ShowAnnotations property of the precedence constraint to ConstraintOption, so if you'd like your package to be self-documenting by displaying the expression, you should do the same thing.


Darren Gosbell said...

I don't have an instance of SQL 2000 handy which I can check this on. But I am pretty sure that you could query INFORMATION_SCHEMA.Tables in SQL 2000 too.

Matthew Roche said...

You are, of course, absolutely correct. Support for the INFORMATION_SCHEMA views was actually added in SQL Server 7.0.

Bredaman said...

I am trying to perform the similar sequence of operation in your example. EXECUTE SQL TASK (single row result set)- I have assigned the value from a 'Select count(*) From Table1' to 'Resultname' and then in the ResultSet I have set up my user variable
(package scope) to take the value. The task that follows is a 'Sequence Container' that contains 3 'Execute Package' tasks. Based on the user variable value from the 'SQL' task either of these packages will be executed. Unfortnately when I check in the variable window after the 'Execute SQl Task' execution the value doesn't change and also my sequence container doesn't work according to what the user variable value should be.

If the user variable is a certain value then the property 'Disable' of one of 'Execute Package' Task will be set to 'On'. Can someone tell me why my user variable isn't getting the assign value?

feersum_endjinn said...

Thanks a lot for this post! I was trying to figure out how to "disable" tasks and this is a much more elegant solution!