How to create DTSGlobalVariables using a variable??

• Keith -222637

  SSC Enthusiast

  Points: 152

  More actions

  May 25, 2005 at 9:00 am

  #164653

  Posted - 05/25/2005 :  09:34:25

  Hi All, I am trying to setup a few DTSGlobalVariables in a DTS package by reading the set of values from a .ini file File format is varitem=varvalue So instead of creating a DTSGlobal as follows DTSGlobalVariables("server1").Value = "path" I would like to be able to place the varitem in a variable and then pass this as the variable name to make the package more versitile and configurable, like the following gVar = varitem gVarValue = varvalue DTSGlobalVariables(gVar).Value = gVarValue Each time I run this script I get a type mismatch, does anyone know how to achieve this???? Thanks in advance H.

• Russel Loski

  SSCertifiable

  Points: 7059

  More actions

  May 26, 2005 at 6:23 am

  #561187

  I couldn't believe what you were saying until I tried it.  My only guess is that the collection cannot handle a variant (all variables in VBScript are variant, not string or int).

   

  This script worked for me:

  '**********************************************************************

  '  Visual Basic ActiveX Script

  '************************************************************************

  Function Main()

     var = "test"

     var1 = "Dog"

      found = false

     for each glob in DTSGlobalVariables

              if var = glob.Name then

                 glob.Value = var1

                 found = true

                 exit for

              end if

     next

  if found = false then

    DTSGlobalVariables.AddGlobalVariable  var, var1

  end if

  msgbox (DTSGlobalVariables("test").Value)

   Main = DTSTaskExecResult_Success

  End Function

  Russel Loski, MCSE Business Intelligence, Data Platform

• pambrian

  SSC Journeyman

  Points: 93

  More actions

  May 26, 2005 at 6:36 am

  #561189

  Have you checked out Dynamic Properties task.  Using the dynamic properties task you can set the global variable straight from the INI file without needed your VB code.  Check out the link on sqlDTS.com below that shows an example to create (via code) a dynamice properties task from the INI file. 

  http://www.sqldts.com/default.aspx?252

  To set the property directly you can create the dynamic property task and then choose the INI file as the source for your global variable.

  Also have you tried explicit conversion such as:

  glob.value=cstr(variable)

  good luck.

  Brian