There is often some confusion about the real value of a global variable at run-time, or the current execution location and security context. This simple script will illustrate what is really going on.

When trying to debug a package and isolate the problem, it is not always practical to use a full debugger, and sometimes it is just simpler to use a more manual approach to isolating problems. This script is just for those situations.

Global variables are one of the key objects for building flexible and dynamic packages, so obviously the value of a variable at run-time is very important. If you are experiencing unexpected behavior, then perhaps one of the variable values is incorrect. Variable names are case sensitive, so a simple typing error can easily cause problems. Your code is using FilePath but perhaps the calling application or package has specified Filepath. This script will highlight the presence of both variables, the original one defined when you created the package and the new one that is created implicitly when you call the package with the incorrect variable name.

The script is written such that it can be pasted into an empty ActiveX Script Task within your package, and at Global variable it will log relevant information to one or more of the three locations supported.

• Message box
• Windows event log
• Text file

Each log location is turned on or off by setting the relevant constant to true or false for that location. There are additional settings for the text file option, the filename itself and the option to overwrite each file, or append to an existing file. A sample of the output is shown below.

Package Information
Package Name: 	GlobalVariable Diagnostics Test
Log Date Time: 	01/01/2004 10:30:00
Current Computer Name: 	TESTBOX
Current User Domain: 	TESTDOMAIN
Current User Name: 	Test User

Global Variables: 2

Index: 	1
Name: 	String
Type: 	String (BSTR)
Value: 	The String Value

Index: 	2
Name: 	MyUnsignedInt
Type: 	Unsigned Int
Value: 	123456

Finally the script itself.

Option Explicit

Const Log_MsgBox = True
Const Log_EventLog = True
Const Log_TextFile = True

Const Log_TextFile_Name = "C:\Temp\GlobalVariableDiagnostics.txt"
Const Log_TextFile_Mode = 2	' ForWriting = 2 or ForAppending = 8

Dim m_sBuffer

Function Main()

	Dim oPkg
	Dim oWshNetwork
	Dim oGlobalVariable

	Dim sName
	Dim sTypeName
	Dim sValue
	Dim iIndex

	iIndex = 1

	Set oPkg = DTSGlobalVariables.Parent
	Set oWshNetwork = CreateObject("WScript.Network")

	m_sBuffer = m_sBuffer & "Package Information" & vbCrLf & _
		"Package Name: " & vbTab & oPkg.Name & vbCrLf & _
		"Log Date Time: " & vbTab & Now() & vbCrLf & _
		"Current Computer Name: " & vbTab & oWshNetwork.ComputerName & vbCrLf & _
		vbCrLf & _
		"Current User Domain: " & vbTab & oWshNetwork.UserDomain & vbCrLf & _
		"Current User Name: " & vbTab & oWshNetwork.UserName & vbCrLf & _
		vbCrLf


	m_sBuffer = m_sBuffer & "Global Variables: " & DTSGlobalVariables.Count & vbCrLf & vbCrLf

	For Each oGlobalVariable in DTSGlobalVariables
		sName = oGlobalVariable.Name
		sTypeName = GetTypeName(oGlobalVariable)

		On Error Resume Next
		sValue = CStr(oGlobalVariable.Value)
		If Err.Number <> 0 Then
			sValue = "<Invalid CStr Value>"
		End If
		On Error GoTo 0
		
		LogVariable iIndex, sName, sTypeName, sValue

		iIndex = iIndex + 1
	Next

	Set oPkg = Nothing


	If Log_MsgBox Then
		MsgBox m_sBuffer, vbOk, "GlobalVariable Diagnostics"
	End if


	If Log_EventLog Then
		Const EventTypeInfo = 4
		Dim oWshShell

		Set oWshShell = CreateObject("WScript.Shell")
		oWshShell.LogEvent EventTypeInfo, m_sBuffer
		Set oWshShell = Nothing
	End If


	If Log_TextFile Then
		Dim oFSO
		Dim oFile
		
		Set oFSO = CreateObject("Scripting.FileSystemObject")
		Set oFile = oFSO.OpenTextFile(Log_TextFile_Name, Log_TextFile_Mode, True)
		oFile.Write m_sBuffer & vbCrLf
		oFile.Close	
		Set oFile = Nothing
		Set oFSO = Nothing
	End If


	Main = DTSTaskExecResult_Success
End Function

Function GetTypeName(ByVal pGlobalVariable)
	Dim sTypeName
	Dim vType

	vType = VarType(pGlobalVariable.Value)

	Select Case vType
		Case 0 : sTypeName = "Empty"
		Case 1  : sTypeName = "Null"
		Case 2  : sTypeName = "Integer (Small)"
		Case 3  : sTypeName = "Integer"
		Case 4  : sTypeName = "Real (4 Byte)"
		Case 5  : sTypeName = "Real (8 Byte)"
		Case 6  : sTypeName = "Currency"
		Case 7  : sTypeName = "Date"
		Case 8  : sTypeName = "String (BSTR)"
		Case 9  : sTypeName = "Dispatch"
		Case 10  : sTypeName = "10 Unknown (ERROR)"
		Case 11  : sTypeName = "Boolean"
		Case 12  : sTypeName = "12 Unknown (VARIANT)"
		Case 14  : sTypeName = "Decimal"
		Case 16  : sTypeName = "Integer (1 Byte)"
		Case 17  : sTypeName = "Unsigned Int (1 Byte)"
		Case 18  : sTypeName = "Unsigned Int (2 Byte)"
		Case 19  : sTypeName = "Unsigned Int (4 Byte)"
		Case 20  : sTypeName = "20 Unknown (I8)"
		Case 21  : sTypeName = "21 Unknown (UI8)"
		Case 22  : sTypeName = "Int"
		Case 23  : sTypeName = "Unsigned Int"
		Case 8204 : sTypeName = "Array (Blank Name)"
		Case Else : sTypeName = CStr(vType) & " Unknown"
	End Select

	GetTypeName = sTypeName
End Function

Sub LogVariable(ByVal iIndex, ByVal sName, ByVal sTypeName, ByVal sValue)
	Dim sBuffer
	
	sBuffer = "Index: " & vbTab & CStr(iIndex) & vbCrLf & _
		"Name: " & vbTab & sName & vbCrLf & _
		"Type: " & vbTab & sTypeName & vbCrLf & _
		"Value: " & vbTab & sValue & vbCrLf & vbCrLf

	m_sBuffer = m_sBuffer & sBuffer
End Sub