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.