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

  • Article

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). 

image

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).

image

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.