SSIS : Writing to the event log

I recently had a discussion with a collegue of mine on how to write information to the event log. If you have tried this before you will see that the DTS.Event.FireError will write an error to the eventlog but the other events (Onwarning, OnInformation) won't. This can be a bit of a headache if you have any application that uses the Eventlog for notifications. To get over this hurdle I would suggest you add a script component to the event you are trying to capture and add the following code to it.

 

Imports System, System.Diagnostics, System.Data, System.Math, Microsoft.SqlServer.Dts.Runtime

' Must Include System.Diagnostics

Public Class ScriptMain

Public Sub Main()

     Try

         ' Write whatever message you want. You may need to investigate what eventID you will use

         EventLog.WriteEntry("Your Source", "Warning the database has gone off line", EventLogEntryType.Warning, 1001, 0)

        ' Report Error

        Dts.TaskResult = Dts.Results.Success

        Catch ex As DtsException

              Dts.Events.FireError(CInt(ex.ErrorCode), Dts.Variables("System::TaskName").Value.ToString, ex.Message.ToString, String.Empty, 0)

        End Try

End Sub

End Class

 

Note: You will need to add the the System.Diagnostics namespace to make this work.