Saving Global Variables within Package

Question

Saving Global Variables within Package

Simon-220850

SSCrazy

Points: 2789
More actions
November 23, 2005 at 5:36 am

#67930

Hi
I have a DTS package that has a global variable that contains a variable if the package fails, however I would like the package to ratain this value after it has run so that I can conditionally perform certain actions after a number of failures
Is there anyway of saving this value. The only way to accomplich this so far I can see is to use an activex task to load the package from within itself increment the value and issue a .savetosqlserver, but am unsure as to whether it will allow me to save the package whilst it is being run

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

Jonathan Stokes SSCrazy Eights Points: 9861 More actions · Answer 1

Consider writing an activex task that creates a text file using filsystemobject and then writes the values of the variables to the text file. That way you can tell exactly what the values were.

For examples, see this post:

http://www.sqlservercentral.com/columnists/sjones/pushftp.asp

------------------------------
The Users are always right - when I'm not wrong!

dougjjj SSCrazy Points: 2081 More actions · Answer 2

I've got a few DTS packages that use global variables in the way mentioned above.

DTSGlobalVariables("GBL_LastSuccessfulRun").Value = CSTR(CurrentDateTime)

Set oPkg = DTSGlobalVariables.Parent

oPkg.SaveToSQLServer "SERVERNAME", , , DTSSQLStgFlag_UseTrustedConnection

It's pretty straightforward and I haven't had any problems since I created them a few months back.

Gosta Munktell SSCertifiable Points: 6004 More actions · Answer 3

Having about the same situation as described I just save the values of the global variables in a table (in this case one column).

I created an Execute SQL Task for each variable as:

insert into variablelog values (?) where ? is the input Parameter for the

global parameter.

mike gercevich SSCrazy Points: 2961 More actions · Answer 4

You would be far better off saving your variables to a table and have your package read them in at the first step of execution. If you call the Package objects SaveToSQLServer method, you will be creating a new version of your package every time it executes... Unless you plan to delete a lot of older versions often in the future, your msdb database will grow to a very large size in the near-term. Also, you will surely leave yourself open for corruption... And have you thought of accidental multiple executions of the package?

-Mike Gercevich

Simon-220850 SSCrazy Points: 2789 More actions · Answer 5

Thanks for the replies, - that's a good point Mike, I had forgotten about that consideration