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

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.

Last modifications : 21.07.2011
Run into errors ralated to Variable Deadlocking. After houres of research, I found out how exectly that stuff works in boith the Control-
and the Dataflow Last modifications are related to Lock/Unlock problems. So in case of an error and a variable was locked before, but
not unlocked, you could run into DeadLock situations. I found a solution after some trouble and do the Unlock which have be done in
Read / Write functions in the finally section, so it guarantied, that each variabel which was locked, is afterwards unlocked.

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.

All needed functions are

VarIterator User/System Variables
VarIterator User/System Variables
    '___________________________________________________________________________________
    ' Outputs all userdefined variables
    '___________________________________________________________________________________
    Function varIterator(ByVal isLogging As Boolean) As String
        Dim content As String = ""
        Try
            'Dts.Events.FireInformation(5001, "varIterator", "Collection : " + CStr(vars.Count), "", 0, True)
            For Each var As Variable In Dts.Variables
                If Not var Is Nothing Then
                    content = cat(content, varProperties(var, isLogging), vbCr)
                End If
            Next
        Catch ex As Exception
            Dts.Events.FireInformation(5000, "varIterator", "Error : " + ex.Message, "", 0, True)
        End Try
        varIterator = content
    End Function

    '___________________________________________________________________________________
    ' Outputs all userdefined variables
    '___________________________________________________________________________________
    Sub systemVariables()
        Try
            Dim sysNames As String = "ContainerStartTime,CreationName,LocaleID,TaskID,TaskName,TaskTransactionOption,CancelEvent,CreatorName,CreationDate,CreatorComputerName,ExecutionInstanceGUID,InteractiveMode,MachineName,OfflineMode,PackageID,PackageName,StartTime,UserName,VersionBuild,VersionComments,VersionGUID,VersionMajor,VersionMinor"
            For Each name As String In Split(sysNames, ",")
                Call varProperties(getVariable("System::" + name, True), True)
            Next
        Catch ex As Exception
            Dts.Events.FireInformation(5000, "varIterator", "Error : " + ex.Message, "", 0, True)
        End Try
    End Sub

    '___________________________________________________________________________________
    ' Show only varibale properties
    '___________________________________________________________________________________
    Function varProperties(ByRef var As Variable, Optional ByVal Islog As Boolean = False) As String
        Dim value As String

        If Not var Is Nothing Then
            If Islog Then
                value = var.QualifiedName.ToString + _
                " = " + var.Value.ToString() + _
                " [readOnly: " + var.ReadOnly.ToString() + ", " + _
                "RaiseChangedEvent: " + var.RaiseChangedEvent.ToString() + ", " + _
                "SystemVariable: " + var.SystemVariable.ToString + ", " + _
                "DataType: " + var.DataType.ToString() + ", " + _
                "EvaluateAsExpression: " + var.EvaluateAsExpression.ToString() + ", " + _
                "ID: " + var.ID + _
                "Description:" + var.Description + "]"

                Dts.Events.FireInformation(&H2000, "varProperties", value, "", 0, True)
            End If

            value = var.QualifiedName.ToString + " = " + var.Value.ToString()
        End If
        varProperties = value
    End Function

    Public Function cat(ByVal buffer As String, ByVal part As String, Optional ByVal separator As String = ",") As String
        If Len(buffer) = 0 Then
            cat = part
        Else
            cat = buffer + separator + part
        End If
    End Function


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

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