SQL Clone
SQLServerCentral is supported by Redgate
Log in  ::  Register  ::  Not logged in

SSIS 2008 Script Component variable gotcha..

In a previous post I showed you how to access variables from within an SSIS script component.  More specifically I showed you how to access variables from within an Script Destination Component.  You can not access the read/write variables using the standard Dts.VariableDispenser that you are familiar with in the Script Task (from the Control Flow tab).  There is a variation on that which allows you to access read_only variables (which I will blog about soon).  The original image I showed, looked like this:


I have a few variables that I want to modify, so I put them in the ReadWriteVariables section and a few variables that I want to only read from, which I put in the ReadOnlyVariables section.   The code I was using was simple enough:


Therein lies the problem.  It turns out that putting any variable in the ReadOlyVariables section that you do not actively use, such as in the example below:


will cause a SQLDUMPER error to be generated.  It took me a few hours to figure out what it was and to find it.  The error is located here:

C:\Program Files\Microsoft Visual Studio 9.0\Common7\IDE\SQLDUMPER_ERRORLOG.log

The actual output, which provides no information that I can figure out, looks like this:



How do you solve the issue?  Put any and all variables you plan on using within your Script Destination Component in the proper section.  Don't look at the variables in terms of their actual 'properties', that is to say even if the variable you are using should be read_only or read/write.  If you plan on using them in the PostExecute() method, you must register those variables in the ReadWriteVariables section.  If you plan on using the variables in the PreExecute() method, then place the variables in theReadOnlyVariablesection and you must use them.  I have not checked to see if the reverse is true, that is putting a variable in the ReadWriteVariables section, but not using it in the PostEecute() method causes the same issue, but I would use the rule I hinted at above.



This blog is syndicated from SSIS - SQL Server Tidbits(http://www.josefrichberg.com/)


Posted by Anonymous on 18 February 2010

Pingback from  Dew Dump – February 17, 2010 | Alvin Ashcraft's Morning Dew

Posted by Jason H on 22 February 2010

Hi Jose,

Did you report this as a bug to connect.microsoft.com or to the Microsoft support team already?

It would be a good idea to do so, since SQL Dumper is catching a fatal exception from the runtime hosting the script that you mention, which could be a bug.

Thanks, Jason

Posted by sqlrunner on 22 February 2010

Hi Jason,

 No I haven't.  I will now, since you have mentioned it.  Thanks, Josef

Leave a Comment

Please register or log in to leave a comment.