Using an SSIS package to monitor and archive the default trace file
I find it frustrating that the SQL Server 2005/2008 default trace is continually overwritten and there is no way to store x number of files or x MBs of data. As a workaround, I developed an SSIS package to monitor the \LOG folder and automatically archive the default trace file whenever a new file is created.
This consists of a FOR LOOP container, a Script Task and a File System Task plus a whole bunch of variables and property expressions.
The guts of the package is really in the Script Task as this is where I use a WMI query to monitor the \LOG folder for .trc files. The file is then renamed (date-time-servername-file) to another folder\share which can be a UNC structure e.g. \\server\share. This way I have a permanent record of the basic server activity for root cause analysis/troubleshooting.
The screenshot below shows the basic structure of the package.
