DTS Success Timestamp
-
Is there a way for a given DTS to read the timestamp of its last successful run? (i.e. When the DTS runs the next evening, I want to read the timestamp from the last succesful run and use that to query my tables for the next set of records to retreive). I thought it would be best if I could just read the last log success date rather than have to store the date somewhere else.
Any suggestions?
-
If you are using a Job to execute your DTS, you can read the sysjobhistory table, and in there you can find the run_date and the run_status of the last job execution.
You only need to retrieve the job ID from the sysjobs table first.
hope it helps...
Rayfuss.-
-
An easier way would be to have an extra step at the end that writes into a dummy table. You can then select the max record from this table for the date and time. Also keeps a longer history than from the sysjobhistory table, which is usually set to keep a finite amount of histories.
You never know when you want to go back and check something.
------------------------------
The Users are always right - when I'm not wrong! -
... or even easier, turn on package logging to SQL Server. Then you have the same as sysjobhistory but without the limit on the history.
Once turned on, DTS logs information to sysdtspackagelog (for the package), sysdtssteplog (for each step) and sysdtstasklog (for individual tasks and custom tasks).
--------------------
Colt 45 - the original point and click interface
-
Thank you for the helpful tips. I was about to go with package logging, but decided to just do a datediff within a 24 hour period instead. If the package fails I have to handle it specially anyway and since the package runs nightly at the same time, 24 hours works fine.
Thanks again for all of the tips!
Viewing 5 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply