Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations strongm on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

DTS Logging

Status
Not open for further replies.

Fsoumia

Programmer
Apr 2, 2011
7
US
I am trying to find a way to set a trigger on sys tables to log a record in my audit table every time a DTS package executes whether manually or through a job scheduler. I have been looking at these sys tables, but could not find which fields actually record the DTS package execution info.. sysdtspackage, sysdtspackaglog, sysdtspackageStep
and sysdtspackageTask

I know you could set a trigger on sysdtspackage table and get the information from sysprocess table to log changes to existing DTS packages and every time a package is created.
I am interested in finding out when a DTS packages runs.?
I could add a SQL task inside each DTS package but that would tedious with more than 400 of them.

I will appreciate any input.

Thank you

 
Hi

Thank you for the link, but I am afraid that's not the solution I am looking for. What I am trying to do is add logging as part of an audit process to several hundreds of DTS Packages in order to find out which ones are actually running. I could write a SQL Task inside a package, which would write a log (date, DTS Name, USerName) to an Audit log table everytime a package execute/runs, but that would be tedious to do the same thing for 100's of them. My idea is to set a trigger on sysdtspackagelog table, which would fire every time a record pertaining to each DTS Package execution info is inserted into that systable. I am just not sure what field or table/tables to reference. I also know that the package logging has to be enabled for each package, just like in the link you shared.
Any ideas?

Thank you

 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top