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?
Thanks.

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.

[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.

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

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

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.

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

[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.

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

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.