SSIS OnError Event Handler - How to Retrieve Error Information (Number, Description, Source)

Submitted Tue, 2006-01-17 19:42

Now that I've figured it out it seems obvious, but I had to struggle a little when creating my first OnError event handler in SQL Server 2005 SSIS. I couldn't figure out how to access the error information that triggered the event. Here's how:

Method A: Expose With the Script Task Editor

You can use the ReadOnlyVariables field on the Script Task Editor to expose these three variables:

ErrorCode
ErrorDescription
SourceName

Then you can read these values from the Dts.Variables collection. Don't use the @ symbol in front of the variable name. To list multiple variables for ReadOnlyVariables, separate them by a comma.

Method B: Get them through code

Some people may prefer to not us ReadOnlyVariables since it creates an dependency external to the script code itself. You can retrieve this variables programatically, but like my LockOneForWrite example it takes some verbose code. Here's some example code I'm using, which uses an extra procedure in the script to retrieve the system variables:

Public Sub Main()
  'Assume success
  Dts.TaskResult = Dts.Results.Success

  Try
    Dim errNumber As Integer
    Dim errDescription As String
    Dim errSource As String

    GetErrorValues(errNumber, errDescription, errSource)
    System.Windows.Forms.MessageBox.Show( _
      errNumber.ToString() + vbNewLine + vbNewLine + _
      errDescription + vbNewLine + vbNewLine + _
      errSource)
  Catch ex As Exception
    ' Displaying the error since this is an example.
    System.Windows.Forms.MessageBox.Show(ex.ToString())
  End Try
End Sub

Private Sub GetErrorValues(ByRef errNumber As Integer, _
  ByRef errDescription As String, ByRef errSource As String)
  
  Try
    Dim vars As Variables

    Dts.VariableDispenser.LockForRead("ErrorCode")
    Dts.VariableDispenser.LockForRead("ErrorDescription")
    Dts.VariableDispenser.LockForRead("SourceName")
    Dts.VariableDispenser.GetVariables(vars)
    Try
      errNumber = CType(vars("ErrorCode").Value, Integer)
      errDescription = vars("ErrorDescription").Value.ToString()
      errSource = vars("SourceName").Value.ToString()
    Catch ex As Exception
      Throw ex
    Finally
      vars.Unlock()
    End Try
  Catch ex As SystemException
    Throw ex
  End Try
End Sub