DTS Logging

[Fsoumia]

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

 

[mutley1]

http://msdn.microsoft.com/en-us/library/aa216427(v=sql.80).aspx

 

[Fsoumia]

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