Variables


Submitted by	Category	Review Cycle	.	Public
Joachim Mutter/sysarc on 05/30/2008 at 04:53 PM	SSiS\Code, SSiS\Doku

Variables

Variables in SSiS are a never ending story. Sometimes you think you have everything understood, but a few days later, you run into an error handling variables.

So after houres of research, I found out how exectly that stuff works in boith the Control- and the Dataflow

ControlFlow

The only possibility to see all varaibles, which are known to the script task was to iterate over then

Dts.Variables

collection. But this collection exist only, until you call the first time the Dts.Variable.Unlock function

which is needed, if you get a new var collection with Dts.VariableDispenser.LockOneForRead(vars, varname)

System and all User -Variables, which are not known to the task, coulld only be read bey the Dts.VariableDispenser.LockOneForRead()

function und after the first unlock, the original Dts.Variavles collectiuon is empty !!!!

So, if you need both access pathes, you must read out at first the variable names of the Dts.Variables collection, so you may be able to

use the programmatical way of accessing variables via Dts.VariableDispenser.LockOneForRead(), which is able to readout every package

variable.

Script Task and the Variable sections

Another interesting stuff is the fact, that sections ReadOnlyVariable and ReadWriteVariables are not really necessary to fillup.

If you use the Code below, you are able to access every variable, if it exist. But if you wite a variable into this section, which is never

created, you will run into an error, becuae the script itself tries to lock this variable during loading time.

So if you want to write a script, which is more flexible, use these sections only for variables, which are absolutley needed.

Returning variables from child packages to parent packages

Think about Parent-Child packages (which are called be the "Package Execution task") more like a funtion call.

All variables of the parent-package are visible and accessible in the child package and act like a call

ByReference

(or more like global variables, this fit better ...)

If you set local variables be SSiS package configuration (via parent paclkage variable) this is more like Call

ByValue.

So changing the values will not returned back to the parent task

Comment from

http://blogs.conchango.com/jamiethomson/archive/2005/03/17/1151.aspx

There are some caveats to this rule. Not all tasks in a child package have access to variables in a parent package.

For example an Execute SQL task can't populate a variable in parent package via the ResultSet tab.

[I have opened a bug on this particular scenario and am hoping it will be fixed by RTM - though I doubt it].

The workaround is to populate a local variable and then use a script task to assign the value of that local variable to the variable in the parent package.

Parent package configurations can be used to produce an almost opposite behaviour. They are used to receive values from a parent package IF the package has been called by a parent package. I like to use the analogy that this is like passing ByValue whereas the scenario explained above is a bit like passing ByRef.

All needed functions are

If Dts.VariableDispenser.Contains(varName) Then ' Should we check or should we throw an error in case of no exisitnace?
If isSystem Then ' Use System way to access variables
Call Dts.VariableDispenser.LockOneForRead(varName, vars) ' Locks only this variable and returns it in the vars collection
result = vars(varName) ' Read variable
vars.Unlock() ' And unlock it
Else ' Script Task collection
vars = Dts.Variables ' Get the tasks variable collection, which is already locked,
' donot unlock it, if you want to iterate over it !!!
result = vars(varName) ' Read variable
End If

VarIterator User/System Variables

Another stuff is a iterator in the DataFlow for ReadWrite Variables.

This is not as easy, because we donot found this collection, but only a derived class me.Variables, which explicitly had implemented public properties for each known variables (this was generated code)

So one possibility to iterate programmativally over this stuff ist to use the Reflection interface:

Imports system.reflection

dim mVars as IDTSVariables90

For Each prop As PropertyInfo In me.Variables.GetType().GetProperties()
mMain.VariableDispenser.LockForRead(prop.Name)
Next
Call mMain.VariableDispenser.GetVariables(mVars)

To do this in the ControlFlow, I see the only change to read out at the very eginning of the code, the variable names from DTS.Variables.
Below you will find a simple class, which does that for you

Variable class

Zur SysArc Homepage ...