developer.* Blogs - Reusable Functions for Reading and Writing Variables in SSIS Script Tasks - Comments

Not Sure...

Daniel Read — Wed, 18 Apr 2007 09:08:57 -0700

Sorry, dave, but nothing jumps out at me other than the use of "RdVar" vs. "ReadVariable"--but that may have just been shorthand you're using for your example.

One possibility does come to mind: is it possible that you've also declared the "SQL" variable as read only in the Script Task dialog box? That might cause the behavior you describe. When using ReadVariable() and WriteVariable() you do not need to (and probably should not) declare the variables on the Script Task "Edit" dialog as available to the script.

Hope that helps,
Dan

Cannot write to package level variable

dave — Tue, 17 Apr 2007 16:59:21 -0700

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

Slight variation when in Script Components

MikeG — Tue, 17 Apr 2007 07:36:24 -0700

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.

Much appreceiated

Arthur Hill — Mon, 02 Apr 2007 09:53:28 -0700

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.

Variable naming in your procedure

Arthur Hill — Mon, 02 Apr 2007 09:14:30 -0700

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?

ssis vars in scripts

Guest — Sat, 31 Mar 2007 13:28:22 -0700

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

SSIS Scripting Shortcomings

Daniel Read — Sun, 18 Mar 2007 15:33:00 -0700

imFrustrated wrote: "Is there no global scripting library that all script task have visibility to?"

Not as far as I can tell, there is not, no. The scripting integration at both design time and runtime is pretty clunky. You will do a lot of copying and pasting. When I was writing a lot of code in SSIS I had a I hope this is one area they will be able to improve in future releases.

Dan

P.S.
See also this post for another example of what I mean by "clunky."

Just so I understand

imFurstrated — Sat, 17 Mar 2007 07:28:21 -0700

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?

Awesome!

Guest — Fri, 16 Feb 2007 12:08:43 -0800

Thank you.

Doh. Got it...

Halvo — Fri, 08 Dec 2006 07:32:44 -0800

As soon as I posted this I found the answer here.

Get variable names?

Halvo — Fri, 08 Dec 2006 06:34:33 -0800

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.

Thanks

Paul — Mon, 16 Oct 2006 06:12:43 -0700

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

Need Help with Variables....

Guest — Sun, 08 Oct 2006 02:24:19 -0700

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...

Not Sure...

Daniel Read — Tue, 03 Oct 2006 12:00:29 -0700

Sorry, Vince, I don't know what to tell you. I've never experienced either of the problems you're describing.

You should not need to have a variable in the ReadWriteVariables if you're using the ReadVariable() and WriteVariable() functions to access it.

Is the variable you're working with defined at package-level scope? I've noticed that it's easy in BIDS to accidentally create a variable at the scope of a lower level container and not realize it.

Sorry I couldn't be more help.

Dan

Tried your WriteVariable function

Vince — Tue, 03 Oct 2006 10:49:31 -0700

Hey Dan,
It's wierd, I tried using your function with my script task editor and still the edited changes I made to the variable don't apply after the package is compiled.

Also when I run the package it stalls, when I edit the properties for script task editor, specifically the "ReadWriteVariables" under script. So if I try to add in a variable under that heading, it doesn't successfully execute the script, but no error msg, just stalls. Only executes when no variables are in the heading "ReadWriteVariables" Has this happened to you?

Thanks again,
Vince

Reusable Functions for Reading and Writing Variables in SSIS Script Tasks

Daniel Read — Fri, 30 Jun 2006 08:26:05 -0700

One of the first of many pains-in-the-butt that SQL Server Integration Services developers will encounter is how from a Script Task in the Control of Flow to write to and read from package variables. I have blogged a bit about the topic of SSIS variables in script tasks in the past, but in this post I'd like to share two custom functions that I copy and paste into all of my script tasks to simplify dealing with variables. If you use these functions you will not need the Script Task Edtior's ReadWriteVariables field to make your package variables available to the script.