Corey Roth [MVP]

I have been messing around quite a bit with SSIS lately and couldn't find an abundance of information on how to read or write to a variable from a Script Component. It actually differs from accessing a variable from a Script Task.

Too get access to the variables collection, basically you create an instance of the IDTSVariables90 object (no idea why it is called that). Then you use the VariableDispense to lock it for reading or writing using the LockOneForRead or LockOneForWrite methods and passing it the name of the IDTSVariables90 object you just created. There is also a LockForRead and LockForWrite method which I believe are used when you want to lock multiple variables at the same time. Once you lock the variable, to read it all you have to do is access it via an indexer and use the Value property. You will of course have to cast it to whatever type you need. Once you are done be sure and call the Unlock method to release the variable.

The following code shows the procedure for reading a variable. Since SSIS script components currently only support support Visual Basic.NET and Marcus loves it so much, the code below is in VB.

Dim dtsVariables As IDTSVariables90
Me.VariableDispenser.LockOneForRead("VariableName", dtsVariables)
Dim MyInteger = CType(dtsVariables("VariableName").Value, Integer)
dtsVariables.Unlock()

Writing to a DTS variable is pretty similar.

Dim dtsVariables As IDTSVariables90
Me.VariableDispenser.LockOneForWrite("VariableName", dtsVariables)
dtsVariables("VariableName").Value = myInteger
dtsVariables.Unlock()

I hope this helps if you have to write a package someday.

Read the complete post at http://www.dotnettipoftheday.com/blog.aspx?id=274