Any way to persist global variables?

Question

Any way to persist global variables?

Mike Austin-398977

Say Hey Kid

Points: 697
More actions
January 22, 2007 at 2:44 pm

#76643

Is it possible to change a global variable in a DTS package and have the change persist after the package has completed processing? If not, is there any way to persist data within the package (as opposed to within a table or text file)?
TIA,
Mike

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

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

Michael Lysons SSCertifiable Points: 6492 More actions · Answer 1

To answer your first question: yes, use a table to store the value. But, that contradicts what you ask in the second question, so sorry about that

You could use a table to store the value(s) and then use Dynamic Properties to set the global variable(s) from those value(s).

Not sure about this one, but perhaps you could have some ActiveX Script that saves the DTS with the newly assigned Global Variable value?

Hugh Thomas Ten Centuries Points: 1018 More actions · Answer 2

I don't know first hand if this will work, but you could try writing a vb script that calls your DTS package and establish the global variable in the vb script. As long as the variable is available to the DTS script, you should be able to use it and then when the DTS is complete, use the variable value in the script at the end.

Like I said, just a thought. It may not be workable.

Jeff Gray SSChampion Points: 10667 More actions · Answer 3

I think that you could write a wrapper that would do that. The properties and methods of the DTS Package object are exposed, so it shouldn't be very difficult.

It would be approximately:

Create a DTSPackage2 object

Call one of the LoadPackage methods

Call the execute method (assuming somewhere in here, the package modifies a global variable)

Call the SavePackage method

Destroy object, etc.

I'd use C# for this if I had to do it. One drawback is that the SavePackage method does not save the visual formatting of the package.

jg

Mike Austin-398977 Say Hey Kid Points: 697 More actions · Answer 4

Thanks, people, for the responses. I have a package that is going to be run several times a day. It will check for the presence of a file. If that file exists, and it hasn't already been processed, then process it. I was hoping that I could store the datetime stamp someplace handy...

Scott Coleman One Orange Chip Points: 27452 More actions · Answer 5

Sounds like something a database table could keep track of very nicely.

Or create a Processed folder, and move the file after processing.

Or set or clear the Archive or ReadOnly properties of the file to indicate it has been processed.