I’ve recently been asked for details regarding the SSIS package I wrote to copy and archive the default trace file which is generated by SQL Server. The contents of the file can be quite useful for troubleshooting or diagnosis purposes.
I’ve updated the package to work with SQL Server 2008 R2 and SQL Server 2012.
The detection of a new trace file is implemented using a Script Task which watches the \Log folder for new files. When a new file is detected it copies the previous file to an archive location. The logic is embedded in Visual Basic.NET (not my personal choice although I was limited in SQL Server 2005 when I wrote the package).
The archive process renames the file with the date and time and then copies the file to a chosen location. I should point out that I use expressions on a few variables to alter outputs such as the filename i.e. date_time_filename.
I also noticed that the service needs appropriate permissions to both access the \LOG directory and also copy to the target directory, in my scenario, this was \\server\share. When I was testing, I launch SSDT (SQL Server Data Tools) using Administrator privileges for testing purposes as a quick workround to permission issues).
Here is the code for the Script Task (apologies for the word wrap, the Live Writer plug-in seems to do this to fit it on the page). I have commented out some of the writeline commands I was using to debug the package when it was initially developed.
I hope you find this useful.
I will try and attach the SSIS package to this post later.