Redux: Using an SSIS package to monitor and archive the default trace file

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.

Code Snippet

1. 'Disclaimer:
2.     'The sample scripts and SSIS package are not supported under any Microsoft standard support program or service.
3.     'The sample scripts and SSIS package are provided AS IS without warranty of any kind.
4.     'Microsoft further disclaims all implied warranties including, without limitation, any implied warranties of merchantability or of fitness for a particular purpose.
5.     'The entire risk arising out of the use or performance of the sample scripts and documentation remains with you.
6.     'In no event shall Microsoft, its authors, or anyone else involved in the creation, production, or delivery of the scripts be liable for any damages whatsoever (including, without limitation, damages for loss of business profits,
7.     'business interruption, loss of business information, or other pecuniary loss) arising out of the use of or inability to use the sample scripts or documentation, even if Microsoft has been advised of the possibility of such damages.
8.  
9.     Public Sub Main()
10.  
11.         Dim vars As Variables
12.         Dim strComputer As String
13.         Dim objWMIService As Object
14.         Dim colMonitoredEvents As Object
15.         Dim objEventObject As Object
16.         Dim strSourceDirectory As String
17.         Dim strServerName As String
18.         Dim strSourceErrorLogDirectory As String
19.         Dim strSourceErrorLogDirectoryWithQuotes As String
20.  
21.         Try
22.             ' Use the SSIS variables in this code for the WMI query
23.             strServerName = Dts.Variables("v_ServerName").Value
24.             strSourceErrorLogDirectory = Dts.Variables("v_DT_SourceLogDirectory").Value
25.  
26.             Console.WriteLine("Servername: " + strServerName)
27.             Console.WriteLine("Monitoring \Log Directory: " + strSourceErrorLogDirectory)
28.             
29.             ' Replace \ with \\\\ which is needed for the WMI query
30.             strSourceErrorLogDirectory = Replace(strSourceErrorLogDirectory, "\", "\\\\")
31.             strSourceErrorLogDirectoryWithQuotes = Chr(34) & strSourceErrorLogDirectory & Chr(34)
32.  
33.             'MsgBox("Server Name: " + strServerName)
34.  
35.             ' Connect to the WMI source
36.             objWMIService = GetObject("winmgmts:\\" & strServerName & "\root\cimv2")
37.  
38.             ' Monitor the error log folder for instances of ERRORLOG.1 as this is the file we want to archive
39.             ' The directory name is parameterised and populated from the SSIS variable
40.  
41.             ' Monitor the directory for new default trace files
42.             colMonitoredEvents = objWMIService.ExecNotificationQuery _
43.                 ("SELECT * FROM __InstanceCreationEvent WITHIN 10 WHERE " _
44.                     & "Targetinstance ISA 'CIM_DirectoryContainsFile' and " _
45.                         & "TargetInstance.GroupComponent= " _
46.                                 & "'Win32_Directory.Name=" & strSourceErrorLogDirectoryWithQuotes & "'")
47.  
48.             objEventObject = colMonitoredEvents.NextEvent()
49.  
50.             'MsgBox("A new file was just created: " + objEventObject.TargetInstance.PartComponent)
51.  
52.             Dim strReturned, strFilePath As String
53.  
54.             strReturned = objEventObject.TargetInstance.PartComponent
55.             strFilePath = Split(strReturned, "CIM_DataFile.Name=")(1)
56.             strFilePath = Replace(strFilePath, """", "")
57.             strFilePath = Replace(strFilePath, "\\", "\")
58.             'MsgBox("Sliced file: " + strFilePath)
59.  
60.             ' strFilePath is C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\LOG\log_nnn.trc
61.             ' Array element (6) is log_nnn.trc, this assumes the above directory structure
62.             Dim strFilename As String
63.  
64.             'strFilename = Split(strFilePath, "\")(6)
65.             'MsgBox("Split: " + strFilename)
66.  
67.             strFilename = System.IO.Path.GetFileName(strFilePath)
68.             'MsgBox("IO.Path: " + strFilename)
69.  
70.             ' If filename like log_ then enter this code path
71.             ' The default trace filename is always log_ so we can rely on this for filename matching
72.             If strFilename Like "log_*.trc" Then
73.  
74.                 Console.WriteLine("A new default trace file was just created in \LOG called " + strFilename)
75.                 Trace.WriteLine("A new default trace file was just created in \LOG called " + strFilename)
76.  
77.                 ' Archive the previous default trace file
78.                 'MsgBox("Default Trace found, now process the file")
79.  
80.                 Dim arrContainer As Array
81.                 Dim intTraceFileNumber As Integer
82.                 Dim strArchiveFileName As String
83.  
84.                 arrContainer = Split(strFilename, "_")
85.  
86.                 'Console.WriteLine(arrContainer(0).ToString)
87.                 'Console.WriteLine(arrContainer(1).ToString)
88.  
89.                 ' Split 1111.trc so we only store 1111 to convert to int
90.                 arrContainer = Split(arrContainer(1), ".")
91.  
92.                 ' This is the active default trace file number
93.                 'Console.WriteLine(arrContainer(0).ToString)
94.  
95.                 ' Convert the active trace file number to int and decrease by 1
96.                 intTraceFileNumber = CInt(arrContainer(0)) - 1
97.  
98.                 ' Convert back to string and create the default trace file name
99.                 strArchiveFileName = "log_" + CStr(intTraceFileNumber) + ".trc"
100.                 'Console.WriteLine("Archiving: " + strArchiveFileName + " to " + Dts.Variables("v_DT_DestinationDefaultTraceDirectory").Value)
101.  
102.                 'MsgBox(strArchiveFileName)
103.  
104.                 'Write the filename to the SSIS variable
105.                 Dts.Variables("v_DT_ActiveFileName").Value = strArchiveFileName
106.  
107.                 Console.WriteLine("Archiving: " + strArchiveFileName + " to " + Dts.Variables("v_DT_DestinationDefaultTraceDirectory").Value)
108.                 MsgBox("Output to SSIS Variable: " + Dts.Variables("v_DT_ActiveFileName").Value + " strFilename: " + strArchiveFileName)
109.  
110.                 ' Indicate success to move on to the next step
111.                 Dts.TaskResult = ScriptResults.Success
112.             End If
113.  
114.             ' Error handling
115.         Catch ex As Exception
116.             Console.WriteLine(System.DateTime.Now.ToString + " - SSIS Script Task Error: " + ex.Message.ToString)
117.             Dts.TaskResult = ScriptResults.Failure
118.         End Try
119.  
120.     End Sub

 

I hope you find this useful.

I will try and attach the SSIS package to this post later.

Comments

• Anonymous
  July 07, 2012
  Thanks for this it will be very helpful. I don't see any need to apologize for it being in VB .NET.
• Anonymous
  July 22, 2012
  Thanks for the usefull contribution having the package would would be great