SSIS: Custom File Logging

I have tried to use the out-of-the-box logging feature provided by the SSIS package under SSIS > Logging.  However this do not allow me to log down some application-related information, such as file path, result count, etc.  As a result, I want to implement a custom file-based logging. 

After research, I find that there are two ways I can implement the custom loggin – use script task to write log to text file or implement your own Integration Services log provider.  For the sake of easy deployment, i choose to use script task to implement my custom file-based logging.  The followings are the steps I am going to do:

1. Create a log folder and assign the log file path
2. Standard log script task to write log
3. Uses event handler to log additional events such as OnError and OnWarning

First, create a log folder using File System Task together with File Connection.  Then concatenate the log file name to the variable in a script task:

Dts.Variables("SWIFTLogFilePath").Value = Dts.Variables("SWIFTLogFolder").Value.ToString() & _
                                          "\\swift" & Year(Now) & _
                                          IIf(Month(Now) < 10, "0", "").ToString() & Month(Now) & _
                                          IIf(Day(Now) < 10, "0", "").ToString() & Day(Now) & _
                                          ".log"

To remind, please ensure your variables are defined in ReadOnlyVariables / ReadWriteVariables. Then you can create another script task to write log to a file.   This is the sample code for writing a log file:

Public Sub WriteLog(ByVal logContent As String)
        Dim logWriter As StreamWriter

        Try
            logWriter = New StreamWriter(Dts.Variables("SWIFTLogFilePath").Value.ToString(), True)
            logWriter.WriteLine(logContent)
            logWriter.Close()
        Catch ex As Exception
            'Unexpected Exception
        End Try
    End Sub

Besides logging, i find that there is a trick/bug found in For Each Container.  You can see that I have duplicated to create log script task block in four different branches.  If I merge these four branches by pointing to the same log script task, the script task will not be executed.  Therefore it doesn’t support branch merging and runs tasks in sequential order only.

The last thing I want to show you is the Event Handler, you can implement the event handler of OnError and OnWarning to capture these information into your log file; just go to “Event Handlers” tab and select Executable and Event you want to handle. Then just put a script task as previous one, but additional information like System::ErrorCode, and System::ErrorDescription can be logged down as well.

Advertisement