Script Tasks as Execution Placeholders

I’ve blogged previously[1] about a technique that involves using the Script Task in SSIS as an "execution placeholder" – essentially just an "empty" task that doesn’t do any work, but which gives you something from which to connect a precedence constraint with an expression, enabling dynamic execution logic downstream. All in all it works pretty well, since the Script Task in its default state returns Success and doesn’t actually do anything. It’s simple, and it works.

Or so I thought.

If you take a look at the comments for the post referenced above, you’ll see that Bart Duncan asked if there was an advantage to using the Script Task as opposed to an empty Sequence container. My response was basically that either one should work fine. Well, this is the case for SSIS in either SQL Server 2005 or SQL Server 2008, but apparently this is not the case for upgrading packages from 2005 to 2008. I have one package in particular (it’s a "master controller" package that is responsible for orchestrating the execution of lots of other packages, so it has a lot more control flow logic than I would usually put in one package) and when I recently did a test upgrade[2], I got a bunch of errors. In essence, they all say this:

Error at Script Task Name [Script Task Name]: No entry point was found during the migration of "ScriptTask_91682d2bfda94fdcb87f72f4c97cd852". Add an entry point to ensure that the script runs.

Apparently (please note that this is an educated guess, not something that I have researched and validated in depth) when you add a Script Task to the SSIS 2005 control flow design surface, the designer does not call out to Visual Studio for Applications (VSA) to precompile the code. This is OK in 2005, because the VSA runtime knows how to handle these "uninitialized" Script tasks, but when upgrading to SSIS 2008, which uses Visual Studio Tools for Applications (VSTA) instead of VSA, the package upgrade wizard and/or VSTA don’t know how to handle this previously-valid scenario.

So I guess I owe Bart an apology, eh?

In any event, there are two simple workarounds that you can perform with your SQL Server 2005 SSIS packages that use this technique:

  1. Replace each "empty" Script Task with an empty Sequence Container. This will eliminate the problem altogether as it eliminates the use of Script tasks entirely.
    or
  2. Open each "empty" Script Task in the VSA editor and then close it back out again, clicking OK to save changes. This will eliminate the problem because it gets the Script tasks into a state that the upgrade wizard knows how to handle.

At first glance, option 1 probably looks more attractive, but it is a little more complicated than that. Remember: the whole reason we’re having this discussion in the first place is because we needed a Control Flow component from which we could drag a precedence constraint with an expression. The problem that this introduces is that if we delete a Script Task in order to replace it with a Sequence Container, the precedence constraint will be deleted as well. Recreating the precedence constraint is simple enough, but then you would need to manually edit each affected constraint to recreate the expression as well. In my "master controller" package I had dozens of these constraints, so manually rebuilding everything didn’t have much appeal – I went for option 2.

[1] Well, I guess I’ve been busy. When I looked up that previous post so I could link to it, I was surprised to discover that it was from May of 2007. I didn’t realize that I’d reached my one year blogging anniversary already. How time flies when you’re having fun…

[2] It’s worth noting that this upgrade was performed using the February CTP of SQL Server 2008; I have yet to make the time to get RC0 installed, as I am somewhat tied up at TechEd and do not have a machine to sacrifice this week.

Advertisements

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, Katmai, SSIS. Bookmark the permalink.

Leave a Reply

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

WordPress.com Logo

You are commenting using your WordPress.com 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