Creating Dynamic Global Variables using VBScript in DTS

[Sweenster]

Hi,
I want to dynamically create DTS Global Variables in VBScript rather than create them up front because I do not know how many I will need each time.

If I create a global variable using the following it works fine. e.g.

DTSGlobalVariables("GV_Name").Value = "AnyValue"

but if I try to do this with a variable it errors out saying type mismatch. e.g.

sName = "GVName"
DTSGlobalVariables(sName).Value = "AnyValue"

Have tried Google & Tek Tips search but no luck.

Any help would be much appreciated.

 

[MacDaddyNickP]

Try using single quotes

 

[Sweenster]

Thanks for your response...
I have tried using single quotes in various forms but without success.

Am I doing something silly?

 

[AlexCuse]

Sort of silly, although I'm sure everyone has overlooked this on many an occasion. Your setting of sname needs to include double quotes around it. so it would be something like this:

sname = """ & GVName & """

Not sure if it's three double quotes or four to get what you want, but one of those should work.

Hope it helps,

Alex



Professor: But what about your superintelligence?
Gunther: When I had that there was too much pressure to use it. All I want out of life is to be a monkey of moderate intelligence who wears a suit. That's why I've decided to transfer to Business School.
Professor: NOOOOOOOOOOOO.

 

[Sweenster]

Thanks have tried that too (3 & 4 quotes)and it still gives same error message saying data type mismatch. Parsing in VBscript is OK just when running?

 

[AlexCuse]

What is the type of Global Variable you are using?



Professor: But what about your superintelligence?
Gunther: When I had that there was too much pressure to use it. All I want out of life is to be a monkey of moderate intelligence who wears a suit. That's why I've decided to transfer to Business School.
Professor: NOOOOOOOOOOOO.

 

[Sweenster]

I guess this could be the answer as I have not created the Global Variable at the point of running the code. I am trying to create them from scratch in VBscript as I do not now how many will be needed each time the pkg is run.

FYI the pkg loops through a folder and for each file that has been modified in the last day it deletes the data from the fact table and then re-imports it. Plan is to create a global variable with the file name of each file thats needs updating.

Cheers

 

[AlexCuse]

Would a looping DTS take too long? IE one that assigns file name to 1 global variable, and then does the update for 1 file?

If that is not an option, perhaps create global variables up to the max number of updated files you can have.
Number these sequentially, and while looping through your folder you can assign file names to each one. Then increment a counter each time a filename is assigned, and that value will let you determine how many global variables will be used by your package, and run your update for each one.

Hope this helps,

Alex


Professor: But what about your superintelligence?
Gunther: When I had that there was too much pressure to use it. All I want out of life is to be a monkey of moderate intelligence who wears a suit. That's why I've decided to transfer to Business School.
Professor: NOOOOOOOOOOOO.

 

[Sweenster]

OK, think I will need to go with creating the max number of global variables.

Thanks very much for your time.