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

SSIS : Using the VariableDispenser object within Script Components

 I have become accustom to modifying package variables within my Script Task using the VariablesDispenser  object.  It is convenient and powerful.  I started working with this object due to issues I found while hacking my way through SSIS2005.  I would put the variables in the ReadOnly or ReadWrite section and would get weird errors attributed to exclusive locking..blah,blah,blah.  I naturally carried this practice over to SSIS2008 and things work the same; in theScript Task.

Getting heavily involved in SSIS development I broke down and decided to do more Script Component work.  Prior to SSIS I had done my absolute best to keep away from VB.Net and along with that, Script Components.  I just didn't like the way VB 'felt'.  To be honest, there is nothing wrong with choosing or avoiding a language based upon personal preference, so long as the language chosen is proper for the job.  Seeing C# as an option in SSIS2008, I decided that would be my language of choice and was once again excited about getting into Script Components to begin developing again.

My first foray back into programming Script Components, I had a hard time using the VariablesDispenser in theScript Component.  I couldn't find the Variables object.  Thinking there had to be an easier way, I went back to using the ReadOnly/ReadWrite Variables property.  This wasn't much easier.  Turns out, these variables can only be accessed within certain sections of your Script Component.  ReadWrite variables are available through the Variables object in the PostExecute() method only:


ReadOnly variables are only available to the Variables object in the PreExecute() method.  I also found some interesting bugs regarding this (see previous post script gotchas).   This worked fine for me, but being more stubborn than smart, I wanted to figure out a way to access these variables the way I was most comfortable, using the VariablesDispenser.  A few sleepless nights later I found what I was looking for; IDTSVariables100 object.


You no longer access the VariableDispenser object through Dts and you no longer pass in the a variable of typeVariables.  Instead you access the package level variables directly through VariableDispenser and pass all of the locked package variables into a local variable of type IDTSVariables100.  

"So what does this buy me?".  First, I am not constrained to have my ReadOnly variables accessible only in thePreExecute() method and my ReadWrite variables accessible only in the PostExecute() method.  This means I don't need local variables to pass information around or keep counts of things to later push out.  This is really only useful inScript Source and Script Destination objects where you don't loop through the methods for each row.  It's also easier to copy/past the code in the above example to move to it multiple scripts vs having to hand code each script'sReadOnly/ReadWrite section.  Last, but not least; personal preference.


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


No comments.

Leave a Comment

Please register or log in to leave a comment.