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.