DTS Logging
DTS Logging
(OP)
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
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
Talk To Other Members
RE: DTS Logging
RE: DTS Logging
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