February 22, 2007 at 12:08 pm
I have a DTS package that exports requisitions to an Excel spreadsheet. I have a global variable that holds that last requisition number. When I execute the DTS package it does not save the last requisition number to the global variable. How can I persist the changed requisition number to the global variable so the next time the scheduled package runs, it uses the last saved requisition number? Thanks.
February 22, 2007 at 12:43 pm
Dan,
I'm no expert, but I think you have to define the global variable in the DTS Designer piece first. Once defined there, then it's values are persisted from one invocation to the next.
Hope this helps.
February 22, 2007 at 1:51 pm
Thanks, but I did define the global variable in the DTS designer; when I run it from the designer and save the package, the value saves, but when I execute it from a job, it doesn't. I haven't found much on it except one that mentioned the package has to be saved in order to save the new value for the global variable. The last step of the package is to assign the latest value to the global variable.
Hopefully, some one will have an answer.
February 22, 2007 at 5:41 pm
Hi, I am not too certain if I understand when you say "when I run it from the designer and save the package, the value saves".
Do you mean the value is saved in an external file? And after you exit from the DTS package, when you return to the package again you could actually use the saved value??? This wouldn't happen if you had not saved the value in an external file and re-read it again when it started to my knowledge. Could you clarify to assist you better.
February 22, 2007 at 10:16 pm
What I mean to say is that if you check the properties of the DTS project, the global variable has the value assigned to it in the last step (from a SQL query that assigns it to the parameter). When you close the designer, the global variable reverts to its initial value. Saving the DTS project when the global variable has the new value, the value gets saved. Hope this clarifies your question.
February 23, 2007 at 9:24 am
Dan,
In my reference, it looks like there are multiple methods available to save the package programatically. Have you tried using those to persist those values?
February 25, 2007 at 4:54 pm
I understand now clearly. You may save the package at the end of the package execution by adding an ActiveX Script Task as below.
Function Main()
Dim oPkg
Set oPkg = DTSGlobalVariables.Parent
oPkg.SaveToSQLServer "Server", "login", "password"
Set oPkg = Nothing
Main = DTSTaskExecResult_Success
End Function
But this method suggests saving your package whenever it's run, if this is what you want.
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply