Wednesday 12 August 2009

SQL Server Integration Services 2008 - Fix for when SSIS Script Tasks Freeze or Run indefinitely without Returning a Result

As you may have seen in my previous posts, I've been having some interesting adventures with SQL 2008 SSIS packages of late. However, the most obscure one hit me today which meant that all Script tasks would run endlessly without error if the code hit ANY exception at all. The problem would happen even when the exception was fully handled within a try...catch blog.

I tried several things like:
  1. Remove all code except for a throw new Exception() - SSIS Script task STILL freezes
  2. Change the project target back to .NET 2.0 from 3.5 (after removing all code) - SSIS Script task STILL freezes
  3. Remove all variables from the Script Task properties - VOILA!

    I eventually worked out that a Package Level variable that i'd defined named "DBConnectionString" would stop exceptions from being bubbled up to the parent package and freeze the task when it was marked as either a Read-Write Variable and a Read-Only variable in the script task properties. I removed this variable definition from the script task properties and all exceptions started to bubble up correctly.

    I tried to reproduce the issue in a completely new package with no success (on the production 64 bit environment and my 32 bit dev machine). It doesn't appear to be related to the variable name or the value of that variable.

    This is just a warning to all those who encounter this unfortunate Script task freezing issue in SSIS. It may also have the appearance of being in an infinite loop as the whole package just sits there and will NEVER complete.

1 comment:

Josef Richberg said...

David, take a look here:
http://bit.ly/InhUE This might give you another way to work with the variables within the script task