Reusable Functions for Reading and Writing Variables in SSIS Script Tasks

I just spent hours chasing my tail with a Variable and SSIS Script Component. If you set the Variable.Expression to True, you can not change its Value in a Script Component. If you try to, it appears to set the value to Nothing.

One of the good blogs about the usage of variables!

I had a scenario like I have to prepare mail messages dynamically when an error happens in a script component present in the control flow. So the onerror event handler, of the control flow script compnent had one more script component for preparing the mail message. Both the script components were trying to use a package level variable errorDescription.

I was using the second method :
a) passing errorDescription as readwrite parameter in the control flow script component
b) passing errorDescription as readonly parameter in the mail message preparing script component

When the control flow script component failed, the mail message script component was called. But the mail message script component remained waiting for aquiring a lock for reading the errorDescription. Finally it ended up unable to handle a deadlock over the package level variable errorDescription.

Then I read this blog by Daniel were he was showing how to write to variable and immediately unlocking it. So I modified the control flow script component. Removed the errorDescrptioon as a readwrite variable and followed out the first methd shown in this blog for writing into variable. It worked out !!!

Thanks, Daniel! This is exactly what I needed for a project I'm working on. Saved me a bunch of time.

- Mike

Thank you from me too. this helped me a lot

I'm trying to write/update an existing variable defined for the scope of a package.
When I execute the Script Task in SSIS using your above code, I can't get the write changes to stick for the variable "AcreFolder". and AcreFolder is NOT set to read only.

From your code above I derived the following:
Note: "AcreFolder" is a user defined string variable that's initially set to "test". and I want to set it to the string value inside FolderPath. Everything run perfect except updating the "AcreFolder" variable.
If you could help me out that'd be great!!!

Thanks

Vince

Dim vars As Variables
Dim FolderPath As String

FolderPath = "C:\" 

Try
   Dts.VariableDispenser.LockForWrite("AcreFolder")
   Dts.VariableDispenser.GetVariables(vars)
   MsgBox(vars(0).ReadOnly)
   MsgBox(vars(0).Value)

   Try
     vars(0).Value = FolderPath
     MsgBox(vars(0).Value)

     Catch ex As Exception
         MsgBox("Unsucessful variable write")
            

    End Try

    Catch ex As Exception

       MsgBox("Unsucessful Variable access")

End Try

I am trying to validate a flat file and insert the values in the SQL Table...It gets tricky here, If while validating, even one record has invalid field I have to abort the package that means no updates or inserts. If everything validates right I have to update the existing record and insert the new ones...
Problem I am running into is I thought I will declare a local variable count the # of errors everytime an error is raised and then decide what to do.As its a local variable my logic will not work as this variable gets initialized for every row.When I try to use the Package variables as I am using a Script component, I get the Read Write Lock error. I cannot use ur functions as they r for scripttask..Any suggestions from u on this...I have spent more than a week researching this...

Thank you. I spent hours trying to figure out why I couldn't update a ReadWrite variable. I still don't know why it won't work, but your solution worked perfectly.

Thanks

Just curious if any of you know of any way to retrieve the names of the user variables which have been setup in a package (via VariableDispenser or any other object).

We have a custom flow task, in which we want to read through each variable that the user has created looking for values with a specific pattern (UNC paths). Considering we do not know which the variables have been named, we have no way of locking them for read.

After a few days of research, we don't think there is a way, but it's hard for me to settle on that answer.

Thank you.

Nice blog post first of all.

I am new to SSIS, is what you are saying that I have to copy these functions to each script task? Is there no global scripting library that all script task have visibility to?

This stuff is a life-saver. I could use your
Dts.VariableDispenser... for a scripts task, but could not for a script component which was reading a table. In your code above (both for read & write) I had to change the "DTs" to "Me", then variable collection was changed also. Here's the variantions on routines:

   Private Function ReadVariable(ByVal varName As String) As Object
        Dim result As Object
        Try
            Dim vars As IDTSVariables90
            Me.VariableDispenser.LockForRead(varName)
            Me.VariableDispenser.GetVariables(vars)
            Try
                result = vars(varName).Value
            Catch ex As Exception
                Throw ex
            Finally
                vars.Unlock()
            End Try
        Catch ex As Exception
            Throw ex
        End Try
        Return result
    End Function

    Private Sub WriteVariable(ByVal varName As String, ByVal varValue As Object)
        Try
            Dim vars As IDTSVariables90
            Me.VariableDispenser.LockForWrite(varName)
            Me.VariableDispenser.GetVariables(vars)
            Try
                vars(varName).Value = varValue
            Catch ex As Exception
                Throw ex
            Finally
                vars.Unlock()
            End Try
        Catch ex As Exception
            Throw ex
        End Try
    End Sub

This is probably not the way to go, but maybe you can improve it. Thanks much

Everything I read says you have to refer to variables by their scope, such as 'User::DirectoryIncoming' or 'System::Something'. In your example above you do not use the namespace qualifier. Why? Is it not necessary?

I have struggled for a week with the 'Can't get lock on variable' error meesage. I used your example and it all works fine. Please send this to MS and tell them to include it in their documentation.

Thanks again.

Hi Daniel:
As the guest poster on 3/31/07 reported, I also found I had to modify the syntax slightly when using these functions in a data flow Script Component (these functions work perfectly in control flow Script Tasks). Don't know why there's a difference, but the "vars" object variable has to be declared as:
Dim vars As IDTSVariables90
And the VariableDispenser appears to be a static class owned by the Script Component. The "Dts" namespace causes an error, so the following works fine:
VariableDispenser.LockForRead(varName)

BTW: One big reason for using these functions is because SSIS seems to mangle variable names passed to Script Components. When using the ReadOnlyVariables property of the Script Component, my variables (ImportFile_Name and ImportFile_Path) are missing their underscores within the Script Component's code (ImportFileName and ImportFilePath). This made it pretty hard to use the "Me.Variables." as documented in online help.

IMO, it's much cleaner and symmetric to use your functions for reading and writing variables, rather than relying on the ReadOnlyVariables and ReadWriteVariables properties. Code is much easier to read and understand when using your functions.

Hi Dan,
I still cannot write to a package level variable in a Script Task. I have 2 variables to create a SQL statement.

  Dim LastRecord As String, sSQL As String
  LastRecord = CType(ReadVariable("LastRow"), String)
  sSQL = "SELECT * FROM MyTable WHERE RecID>" & LastRecord
  WriteVariable("SQL", sSQL)
  sSQL = CType(RdVar("SQL"), String)
  MsgBox(sSQL)

I'm able to read the value of LastRecord but when I appended to sSQL and call WriteVariable, the MsgBox () shows only the initial value of the Package variable (SQL). Apparently it did not save it... any help???
Thanks in advance