DTSGlobalVar lose its value in VBScript

willi · May 13th, 2005, 08:19 AM

Hi, all,
i have a strange problem with the following VBscript-Code:

Code:

Function Main()
    ...

    ''''''''''DTSGlobalVariables("myGlobVar").Value = "xyz"

    If DTSGlobalVariables("myGlobVar").Value = "xyz" Then

        sTmpVar = DTSGlobalVariables("myGlobVar").Value
        retVal = doSomething( sTmpVar )
        MsgBox retVal

    End If


Function doSomething( sTmpVar ) 
    ...
    doSomething = retVal
End Function

The Problem is with the following call from a SQL-Stored Procedure:

dtsrun /S myserver /U sa /P sa /N MyPacket /M sa /A myGlobVar:8="xyz"

At runtime, the If-Condition gets true, but then sTmpVar is NULL
and I get the report of a runtime error in doSomething().
The DTSGlobalVariable is set correctly in the Package Properties
and when I set the DTSGlobalVariable inside the Main-Function of this
script, it works.

Thanks for every tip
willi

joefawcett · May 20th, 2005, 03:50 AM

Other than re-arrange the code to:

Code:

Function Main()
    ...

    ''''''''''DTSGlobalVariables("myGlobVar").Value = "xyz"
    Dim sTmpVar
    sTmpVar = DTSGlobalVariables("myGlobVar").Value 
    If sTmpVar = "xyz" Then    
         retVal = doSomething( sTmpVar )
        MsgBox retVal

    End If

I can't see why your code fails.

Are you sure that you haven't mistyped an identifier? Do you have Option Explicit set?

--

Joe (Microsoft MVP - XML)

deano · August 29th, 2005, 03:26 PM

I have the same problem. In an active X script, I assign today's
date to gv_ImportDate, after having created the global variable. Then I display it in a message box to test it. Msgbox displays the
correct date.

However, I cannot use the variable in the where clause of a SQL
query. I am trying to return data from JET. It succeeds if I
hard code my where clause to "where info_dt = #8/28/2005#"

Any help out there please?