Setting Global Variables in a Script Task, HOW?

Setting Global Variables in a Script Task, HOW?

• johnvarney

  Mike, it also works ("User::") even if you don't use the Script Task Editor (I posted an example earlier in this thread). Maybe it's just the "newness" of SSIS for me or I'm just too old to learn new tricks, but my take is the setting and using of Global Variables in SSIS is WAY more complicated than it needs to be.

  In DTS it seemed a whole lot more simple: Dts.GlobalVariables("varName").Value = "xyz" (probably don't have the exact syntax correct, but you get the drift...). The locking is a huge improvement over DTS so I have to take the good with the added learning curve.

  Would be nice if the BOL indicated that if you set the ReadOnly, WriteOnly properties in the Script Editor, you needed to use a certain coding convention (i.e. Dts.Variables("varName").Value = "xyz") but you needed to use a different convention (i.e. the Dts.VariableDispenser --> vars(0).Value = "xyz") if you wanted to control the Locking in your code...

  Thanks to you and Phil for your help (and patience!). I'm just building a library of working examples (that actualy work for me!)so I can refer to them as I get more and more acquainted with SQL 2005...

  
  
  

• Henrik Dahl

  Dan,
  Have you searched this forum for assigning values to variables There are several posts related to this topic, each with a different spin on things.
  
  Phil
  
  
  

• xRuntime

  Well, this is Jamie's example, and it doesn't match yours:
  
  

  Imports Microsoft.SqlServer.Dts.Runtime

  Public Class ScriptMain

  Public Sub Main()

  Dim vars As Variables

  Dts.VariableDispenser.LockOneForWrite("vMyVar", vars)

  vars(0).Value = "Hello World"

  vars.Unlock()

  Dts.TaskResult = Dts.Results.Success

  End Sub

  End Class
  
  
  

• mkfl

  Phil / Mike, thanks for the help...

  Now I'm actually confused (seems to happen alot since I delved into SQL 2005!). I actually placed the "User::" tag in front of my variables and it worked. Oh well, I have enough to play with for now (I especially like the functions for the read / write capabilities). I'm really just trying to start refreshing my scripting skills (or obviously lack thereof)...

  You actually can put the "User::" in on the Script Task Editor and it works...

  If I define the variables "User::varFTPDestPath" and "User::varFTPFileName" as ReadOnly and "User::varFTPDestPathFileName" as ReadWrite in the Script Task Editor, the following works:

  Public Sub Main()

  Dim strFTPDestPath, strFTPFileName As String
  strFTPDestPath = Dts.Variables("User::varFTPSourcePath").Value.ToString
  strFTPFileName = Dts.Variables("User::varFTPFileName").Value.ToString
  Dts.Variables("User::varSourcePathFileName").Value = strFTPDestPath + strFTPFileName
  MsgBox("varSourcePathFileName = " + Dts.Variables("User::varSourcePathFileName").Value.ToString)
  Dts.TaskResult = Dts.Results.Success
  

  End Sub

  
  
  

• MON205

  Hi Dan:

  You're right on this, but use the "plain" name of your variables, instead of using the
  "User::" prefix. Here's a simple WriteVariable procedure that does what you're trying
  to do. This code is virtually identical to yours except how the variable is referenced:

  Private Sub WriteVariable(ByVal varName As String, ByVal varValue As Object)
  
    Try
  
      Dim vars As Variables
      Dts.VariableDispenser.LockForWrite(varName)
      Dts.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 'WriteVariable

  Use this syntax to call this routine:

  WriteVariable("MyVariableName", AppropriateValueForVariable)

  I got this from Daniel Read's site: http://www.developerdotstar.com/community/node/512

  Here's the "ReadVariable" function that uses the complementary logic (also from same site):

  Private Function ReadVariable(ByVal varName As String) As Object
    Dim result As Object
  
    Try
      Dim vars As Variables
  
      Dts.VariableDispenser.LockForRead(varName)
      Dts.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 'ReadVariable

  Because the return value is an object, you'll have to CType it into something you can use:

  SomeVariable = CType(ReadVariable("Global_Variable_Name"), VariableDataType)

  Just omit the "User::" stuff. I think that has to do with the SSIS environment knowing where to
  get the referenced object, and is quite different than directly referencing a member of the
  VariableDispenser collection through code.

  Personally, I prefer directly accessing variables in script tasks, rather than relying on the
  ReadOnlyVariables and ReadWriteVariables properties. Well-written code is self-documenting,
  and you don't have to keep flipping back and forth between the Script Task's dialog and code
  to use global variables. Until they give script tasks "real" intellisense, where it can tell you things
  about the package's environment (like which variables have been specified in the Script Task's
  dialog), I'd just as soon use code that lays it all out for me like this.
  
  Hope this helps!

   

   

   

   

  
  

• Ecrofirt

  mike.groh wrote:

  Hi Dan: You're right on this, but use the "plain" name of your variables, instead of using the "User::" prefix. Here's a simple WriteVariable procedure that does what you're trying to do. This code is virtually identical to yours except how the variable is referenced:

  
  

  Ahh... Even I overlooked the User:: prefix... Yep, try that. Even in the Read(Only|Write)Variables box, you wouldn't want the "User::" prefix.
  

  
  
  

• Alvin Kuiper

  Have you read any of Jamie Thompson's stuff
  
  http://blogs.conchango.com/jamiethomson/archive/2005/02/09/964.aspx
  
  
  

• Victor BA

  Phil, I did search, even tried some of the coding I found (that's how I got started). Just can't seem to tweek the script to work...
  
  

• LeoXue

  Hi Dan:

  That's a surprise -- I'd always thought that the "User::" prefix was so that the
  package would know which container to use to find an object. It must work for
  you because you've specified the variables as the ReadOnlyVariables and
  ReadWriteVariables properties for your script task, and the interpreter strips
  off the User:: prefix when it evaluates the reference.
  
  Pretty cool! One less thing to worry about, eh

  
  

• CJW99

  Phil, thanks...  I've been playing and the following works.  I found other code (examples in this forum) that used the format vars("variablename").value = "xyz" in this forum.  This seems more intuitive for me so that is why I posted the original question, but if "it don't work, it don't work..."

  I had two other questions in my original post...

  1). Do you know how to the enumerate Dts.VariableDispenser.GetVariables container   If so, please share

  2). Do we always need to preface variables with "User::" or "System::"   If answer is no, any idea on when we should/shouldn't

  If you know the answers to either of the above, please elaborate...

  Thanks  Dan

  Public Sub Main()

  Dim vars As Variables
  Dim strFTPDestPath, strFTPFileName As String
  Dts.VariableDispenser.GetVariables(vars)

  ' Lock for Read/Write the variable we are going to use
  Dts.VariableDispenser.LockOneForRead("User::varFTPSourcePath", vars)
  strFTPDestPath = vars(0).Value.ToString

  Dts.VariableDispenser.LockOneForRead("User::varFTPFileName", vars)
  strFTPFileName = vars(0).Value.ToString

  Dts.VariableDispenser.LockOneForWrite("User::varSourcePathFileName", vars)
  vars(0).Value = strFTPDestPath + strFTPFileName

  MsgBox(vars(0).Name + " " + vars(0).Value.ToString)
  vars.Unlock()

  Dts.TaskResult = Dts.Results.Success

  End Sub
  

   

  
   

  
  
  

• AlanKohl

  goduncan wrote:

  We just had SQL Server 2005 installed and I too am learning SSIS on the fly. My problem is assigning values to global variables in the Data Flow Script Component transformation. The examples shown by Jamie and others referencing variables using the DTS object does not work because the DTS object is not available in the Script Component. Is this a install configuration that was overlooked or is there another method to use global variables in the Script Component Appreciate any assistance given. Gerald Duncan

  
  
  Right. This thread was about the Script Task, not the Script Component. The Script Component exposes them through two ways. One, you input the variables on the parameter page of the script component wizard and then reference them by: Me.Variables.YourVariableName. The other method is to load up variables using the VariableDispensor method. Though, you won't be able to actually *write* to the variable row by row, you'll have to use the PostExecute() method of the script component. You can search this forum for examples.
  
  As it is, if you have further questions regarding the script component and variables within, please start a new thread as this one deals with the Script Task.
  
  
  

• JaBaVe

  Yes I have (read some of Jamie's blogs)... I tried to grab bits and pieces from the forum, and what I found on the net. I was hoping that someone could see a rookie mistake I've made (that's what I think anyway) and give me a hint/clue on the right direction...

  
  
  

• Chrismanster

  We just had SQL Server 2005 installed and I too am learning SSIS on the fly. My problem is assigning values to global variables in the Data Flow Script Component transformation. The examples shown by Jamie and others referencing variables using the DTS object does not work because the DTS object is not available in the Script Component.

  Is this a install configuration that was overlooked or is there another method to use global variables in the Script Component

  Appreciate any assistance given.

  Gerald Duncan

  
  
• Setting Global Variables in a Script Task, HOW?