I've been asked to look at implmenting a auditing process on 2 sql 08 boxes. I've read lots of info about the subject but I'm still a little unsure how best to implement this. I'd ideally like to have the auditing files inserted via a ssis package into a new sqldb on it's own server
I only have to audit any select,updates,deletes on 10 tables across the two servers and I need to log all the processes done my sysadmin.
The question I'd like some advice on or pointers in the right direction is
1. How do I grab the correct file in the ssis package? - for example what happens when the file rolls over? And I'm I best just saving this to file rather than app log or tran log?