Bandwidth Monitor
SQLDTS.comSQLIS.comSQLIS Wiki
DTS Global Variables and Visual Basic .Net
By Douglas Laudenschlager

Conclusions

1. In order to change the value of a DTS Global Variable of type String from VB.Net code, the variable must be programmatically removed from the collection and added back with its new value.

2. In order to change the value of a DTS Global Variable of type Date, the new value must be explictly cast as type System.DateTime; otherwise an error occurs.

Test Package: Single ActiveX Script Task

Package set to "fail on first error"

Global variables:
   GVBoolean: True
   GVCurrency: 4.95
   GVDate: 5/28/2002
   GVDecimal: 9.9999
   GVInteger: 4
   GVString: Douglas

Function Main()
 Dim v
 Dim msg
 For Each v in DTSGlobalVariables
  msg = msg & v.Name & ": " & v.Value & vbCrLf
 Next
 MsgBox msg
 Main = DTSTaskExecResult_Success
End Function

Test Application with Workaround: VB.Net Console Application

Imports System.Collections.Specialized
 
Module Module1
 
    Sub Main()
        Dim pkg As DTS.Package2
        Dim v As DTS.GlobalVariable2
        pkg = New DTS.Package2()
        pkg.LoadFromSQLServer("(local)", Nothing, Nothing, _
		DTS.DTSSQLServerStorageFlags.DTSSQLStgFlag_UseTrustedConnection, _
		Nothing, Nothing, Nothing, "GlobalVarsNet", Nothing)
        Console.WriteLine("AFTER LOAD/BEFORE WORKAROUND")
        For Each v In pkg.GlobalVariables
            Console.WriteLine("{0}: {1}", v.Name, CType(v.Value, System.String))
        Next
        'Workaround
        Dim nvc As NameValueCollection = New NameValueCollection()
        For Each v In pkg.GlobalVariables
            If TypeName(v.Value) = "String" Then
                'Save variable name and value
                Dim tmpName As String = v.Name
                Dim tmpValue As String = CType(v.Value, System.String)
                nvc.Add(tmpName, tmpValue)
                'Remove variable from collection
                pkg.GlobalVariables.Remove(v.Name)
                Console.WriteLine(" WORKAROUND: Removed {0}", v.Name)
            End If
        Next
        'Add string variable(s) back to collection
        Dim i As Integer
        For i = 0 To (nvc.Count - 1)
            pkg.GlobalVariables.AddGlobalVariable(nvc.Keys(i).ToString, "Replaced string value here")
            Console.WriteLine(" WORKAROUND: Added {0}", nvc.Keys(i).ToString)
        Next
        Console.WriteLine("AFTER WORKAROUND")
        For Each v In pkg.GlobalVariables
            Console.WriteLine("{0}: {1}", v.Name, CType(v.Value, System.String))
        Next
        'Change values
        pkg.GlobalVariables.Item("GVBoolean").Value = False
        pkg.GlobalVariables.Item("GVCurrency").Value = 5.95
        pkg.GlobalVariables.Item("GVDate").Value = CType("6/23/2002", System.DateTime)
        pkg.GlobalVariables.Item("GVDecimal").Value = 7.7777
        pkg.GlobalVariables.Item("GVInteger").Value = 3
        'pkg.GlobalVariables.Item("GVString").Value = "This raises an error"
        Console.WriteLine("AFTER CHANGES")
        For Each v In pkg.GlobalVariables
            Console.WriteLine("{0}: {1}", v.Name, CType(v.Value, System.String))
        Next
        pkg.Execute() '<-- Execution fails here if line that changes string variable is uncommented
        pkg.UnInitialize()
        pkg = Nothing
        Console.Read()
    End Sub
 
End Module

Disclaimer: This document is provided "AS IS" with no warranties, and confers no rights.