Expert SQL Server™ 2005 Integration Services

7.2. Logging

SSIS has extensive logging built into its architecture to log into a variety of destinations, but typically you would log into a SQL Server table or a text file. If you were to log into a SQL Server table, you could have multiple packages all logging into the central table.

The standard table that SSIS will use for logging is called sysdtslog90. A big weakness of this logging provider for SQL Server is that you cannot log into a custom table, and some of the columns that would be real useful for ease of use aren't there. That's typically why you should use event handlers for logging. Regardless, logging using the logging providers is extremely easy to configure, and it does contain more information in some cases than can be obtained through an event handler like the pipeline events.

This section walks you through some common scenarios and shows you how to get over some of the obstacles with the logging providers. Let's start by creating a new package called Logging.dtsx. Drag over a single Script Task and name it Write to Log. Inside the script of the Script Task, add the following code inside the Main subroutine:

Public Sub Main()
      Dim emptyBytes(0) As Byte
      Dts.Log("Script error has occurred.", 10003, emptyBytes)
      Dts.TaskResult = Dts.Results.Success
End Sub

Close the Script Task to save the code. This code will write to whatever log provider you eventually select. This type of logic could be used to conditionally write data to your logging provider.

Your next step ...