Finding last execution date of DTS Package

Question

Finding last execution date of DTS Package

Erich Brinker

SSCrazy

Points: 2973
More actions
June 2, 2005 at 11:53 am

#68007

Hello All
Can someone tell me how to find the date a DTS package was last executed? I am trying to track down something that is calling dtsrun at a strange time. I have checked all jobs that call DTS packages and nothing is scheduled at that time, but there is one that gets called from a service that I can't tell when it was last run.

Any help would be appreciated
Thanks
Erich

Viewing 12 posts - 1 through 11 (of 11 total)

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

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 1

This should help you :

Select * from msdb.dbo.SysJobHistory where job_id = '42ADD00B-46AB-4EF5-AD59-DE476FA638D2' order by run_date desc, run_time desc

Erich Brinker SSCrazy Points: 2973 More actions · Answer 2

Thanks for the help.

I ran what you posted and it returned no results. Am I missing something?

-Erich

ChrisMoix-87856 SSCertifiable Points: 7288 More actions · Answer 3

If it is being run interactively (and not by a scheduled SQL Server Agent job) the only way to determine when it is being run is to enable package logging (inside the DTS pakage, go to Package Properties, Logging, and enable package logging). That way it will log the execution (and status of all of the steps) whenever the package is run.

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 4

For my query to work you need to change this '42ADD00B-46AB-4EF5-AD59-DE476FA638D2' to your package id.

Erich Brinker SSCrazy Points: 2973 More actions · Answer 5

Thanks,

I realized after I posted that is probably what you meant. But by then it was already up in lights.

Thanks Again

Erich

ChrisMoix-87856 SSCertifiable Points: 7288 More actions · Answer 6

If it wasn't run by a scheduled job, it isn't going to have an entry in SysJobsHistory,

"I have checked all jobs that call DTS packages and nothing is scheduled at that time, but there is one that gets called from a service that I can't tell when it was last run."

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 7

Maybe a simplistic approach, but just to add another option to the logging method... Maybe you could add a step that logs each execution in an history table... untill you can figure this out. But I'm sure that the other logging approach offers more information in its logs.

Habib Zmerli Mr or Mrs. 500 Points: 559 More actions · Answer 8

You have 2 options :

Change the dts package properties to enable logging to SQL SERVER. When the package is executed, an entry will be added to the table : msdb.dbo.sysdtspackagelog and one entry per pâckage task in the table msdb.dbo.sysdtssteplog. If the package is itself saved to SQL SERVER, you can right click on it in Enterprise Manager and access the execution history.
You also have to access the package properties and in the logging tab you choose to write completion status to the Windows Application Event Log. Analyzing the event log will give you clues on package execution history.

Good Luck

HABIB.

Kindest Regards,

Habib Zmerli (MVP)

Rick Lowrey Right there with Babe Points: 785 More actions · Answer 9

Save the DTS package under a new name, delete the old one, and see if anybody complains. If there is someone running it manually with DTSRUNUI on the command line, you should find out quickly!

(Note: I would not actually recommend doing this...it can potentially disrupt a business process somewhere, as well as create all sorts of havoc and bad feelings among the user community. But it sure would be fun!)

ChrisMoix-87856 SSCertifiable Points: 7288 More actions · Answer 10

ChrisMoix-87856

SSCertifiable

Points: 7288

June 3, 2005 at 9:08 am

#562946

Rick, best solution yet!

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 11

Another solution... have you mailed you programmers to ask 'em who as using that dts (or which application)??

If they all reply :" I don't use it", then you might use the rename option... but then again it might affect business operation so definitly a last resort.