posted 2/16/2010 by sqlrunner - Views: [6246]
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.logThe actual output, which provides no information that I can figure out, looks like this:
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 theReadOnlyVariables section 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.