Global variable does not update upon execute DTS

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Import/Export (DTS) and Replication (2000)

Global variable does not update upon execute DTS

Author

Topic

renugrover
Starting Member

5 Posts

Posted - 2007-10-29 : 16:39:35

I have a DTS package which has 1 global variable GLB set to value = 1 using the Package Properties of the DTS package.

Next I have an ActiveX script with MsgBox(....GLB...)
Next I have a Dynamic Properties Task that is set to change that value to a constant = 2.
Finally another ActiveX script with MsgBox(....GLB...)

When I run this package within EM while the package is OPEN by hitting the green triangle button on top of the GUI. I get 2 message boxes
1. GLB = 1
2. GLB = 2
as expected. Now when I look at package properties/Global variables I can see the GLB = 2. AWESOME...does the job!

NOW I reset the whole above DTS as before. Save and close the DTS package.
Now from EM, I use right mouse button/Execute package command, I get 2 message boxes:
1. GLB = 1
2. GLB = 2
as expected. HOWEVER when I look at package properties/Global variables I see the
GLB = 1.
In this situation it did not save the change to my GLB.

I am running the script on the right server with Admin priviledges. PLEASE HELP!!!

dataguru1971
Master Smack Fu Yak Hacker

1464 Posts

Posted - 2007-10-29 : 16:59:25

I have a number DTS jobs that do extensive Global variable assignment which essentially do :

intNew = DTSGlobalVariables("intTest") + 1

DTSGlobalVariables("intTest") = intNew

SO that whatever variable is stored increments and then is Reassigned to the new number as above

How is your script assigning the new property? and make sure there aren't "duplicate" properties. The GB's are case sensitive and you need to be careful sometimes.

renugrover
Starting Member

5 Posts

Posted - 2007-10-30 : 13:24:06

Thanks for offering to help!
In one scenario I have exactly the same code as yours -

DTSGlobalVariables("LastRunTime") = Now() - setting it to current datetime
RESULT - updates it when I run it with package open and retains New value. Seems to update the Global var if run from a SQL Job or outside the pkg with RMB click and execute option but does not RETAIN the new value. Debug message boxes show me OLD and NEW values but New is not retained.

Another scenario as described in first post, I am changing the value of the Global var using Dynamic Properties Task and using a constant value to change it to. Same result as above. Runs OK inside the pkg, does not Retain New value when run from outside even though the message boxes tell me value has changed.

I need to schedule the package to run ever so often for records in the DB created between LastRunTime and currentTime. But if the LastRunTime variable does not retain its value when run as a Job I will get all the records from the first time it runs everytime.

PS: I am aware of the case sensitivity and I am taking care of it.

dataguru1971
Master Smack Fu Yak Hacker

1464 Posts

Posted - 2007-10-30 : 14:33:16

YOu could just schedule the job via SQL agent and have a second step in the agent job update the timestamp as an alternative. My packages all retain the new value whether run using designer and play button, SQL agent or otherwise run.

renugrover
Starting Member

5 Posts

Posted - 2007-10-30 : 15:22:11

I did schedule it using SQL Agent as well. Did not help. So are you saying that after you run your package using SQL Agent and then open the pkg, go to PKG properties/Global Variables, your global variables have the last value that your package sets them to??
I am puzzled because debugger msgboxes suggest that the vars are being changed as expected along the way as the script runs but after it is all over the Pkg properties does not display the last updated value of the variable. It shows my initialized value prior to running the PKG.

Grrrrr!!!
Can steps on agent job access the global vars of the package??

dataguru1971
Master Smack Fu Yak Hacker

1464 Posts

Posted - 2007-10-30 : 16:42:19

I have a bout 2 dozen jobs that run on schedule every month, most of which use dynamic global variable assignment at run time. All of them require incremental increases. After running my packages have the most recent variable stored for the ones I assign as above.

Most of my dynamic property assignment is done based on either queries from the DB or a date formula of some kind, but even though I haven't opened the DTS packages in months, the most recent year and month assigned remain visible in the global properties.

I just set up a quick one.

DTS job has one global variable called IntTest as an integer with a starting value of 1

The active x script is the only job in the package.

and is:

Dim intVar

intVar = DTSGlobalVariables("IntTest") + 1

Msgbox intVar

DTSGlobalVariables("IntTest") = intVar

MsgBox DTSGlobalVariables("IntTest")

I ran the job 5 times in design with the numbers incrementing properly. I saved the package and right click>execute in from EM and it incremented properly with 6 showing, then 7 then 8.

I scheduled the job to run every 5 seconds. I waited about 30 seconds and ran it manually again the number on both msgboxs was 14

It seems that something is awry on your end. Double check the case of each variable and the statements, the slightest error would produce bad results.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources