Conditional Task Execution

As we saw in the last post, the combination of SSIS Expressions, variables and precedence constraints gives us a lot of power for determining what execution path our package should take. But what happens if the logic we need to implement isn't quite so straightforward, such as:

• It's the very first task in the package that you need to execute conditionally.
• The variable on whose value the task must be conditionally executed is set declaratively via an Expression or Package Configuration, and not procedurally via an Execute SQL (or other) task like we did in the last blog entry.
• The conditionally executed task is part of a larger control flow, and there are tasks that must execute beneath it, either when it executes successfully, or when its execution is skipped due to the variable value at runtime.

These situations don't pop up every day, but they do happen often enough for SSIS developers to have a handy addition to their toolbox: The Script Task.

But wait, you say - you don't need to write VB.NET code just to get around this problem, do you?

And, of course, the answer is no - no code is required. The Script task has hidden talents above and beyond its affinity with .NET code: it can sit around and do nothing at all except take up space.

Now tell me that doesn't sound like some .NET developers you know?

In any event, the Script task comes in handy for situations like this because (unlike most other tasks) it is valid in its default state when dragged onto the control flow design surface. You don't need to change anything: Because its default script returns success, it just works - without actually doing any real work, of course - by default.

This means that if we need a "Placeholder" task to serve no other purpose other than to have a precedence constraint drawn from it (as shown in the first two bullets, above) the Script task is what we need.

It also means that the Script task - when placed inside a Sequence container - does what we need in the situation described in the third bullet, above. Take a look at the image below. In this package, we only want to run the Execute Package task if a flag is set to True via a configuration file, and it depends on successful execution off other Execute Package tasks before it, and we need to have more Execute Package tasks below it execute either when it completes successfully or when the flag is set to False. Set up as you see below, it works like a charm.





So, when you're looking for the right tool for the job of not actually doing anything, think of your friendly neighborhood developer. Wait, I mean friendly neighborhood Script task. That's right...