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…


About ssimagine

My name is Matthew Roche, and I am a Senior Program Manager with the SQL Server product group at Microsoft. I work on Master Data Services and Data Quality Services, and have previously worked on SQL Server Integration Services. Although I work for Microsoft and will be posting on technical topics, I want to stress that this is a personal blog, and any opinions posted here are mine and mine alone. I built my career around SQL Server and Microsoft technologies for well over a decade before I joined Microsoft as an employee, and I plan on using this blog to share my personal experience and opinions. They may well be shaped by my experience on the SQL Server team, but they’re still mine, and not that of Microsoft, disclaimer, disclaimer, etc., etc..
This entry was posted in BI, SSIS. Bookmark the permalink.

6 Responses to Conditional Task Execution

  1. bartd says:

    This comment has been removed by the author.

  2. bartd says:

    I believe script tasks have some memory and perf overhead, even if they don’t contain code. Why not just use a sequence container when you need a no-op executable?

  3. Bart – A Sequence container would also definitely work. I honestly cannot speak to the precise overhead associated with using a code-free Script Task, but I can say that it is trivial. I’ve always used empty Script Tasks for this technique and have never had any problem.

  4. bartduncan says:

    Thanks for the confirmation! I wasn’t sure if there was some advantage to the script task here. Very useful post, BTW — thanks!

  5. bartduncan says:

    Thanks for the confirmation! I wasn’t sure if there was some advantage to the script task here. Very useful post, BTW — thanks!

  6. Update: Take a look here for a little more information about using this technique and how it might impact your upgrade from SSIS 2005 to SSIS 2008.

Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s