Post reply

SSIS Event handlers - Using script task to retrieve the error information

  • February 4, 2008 at 11:15 am

    #119806

    Hi,

    In our SSIS packages we use script tasks to send alerts when a step fails.

    I don't know where to get the specific error descriptions, I want to be able to email out the same info that gets stored in the sysdtslog90 table (when logging to sql table).

    Has anyone used the DTSError class? I need some help with accessing these properties in my script task.

    thanks - tom

  • February 4, 2008 at 11:33 am

    #774851

    You could use a script task in an onError event handler. In this example, using string variables representing ErrNumber, ErrSource, and ErrDescription.

    Imports System

    Imports System.Data

    Imports System.Math

    Imports Microsoft.SqlServer.Dts.Runtime

    Public Class ScriptMain

    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)

    Dts.Variables("ErrNumber").Value = errNumber.ToString()

    Dts.Variables("ErrDescription").Value = errDescription.ToString()

    Dts.Variables("ErrSource").Value = errSource.ToString()

    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

    End Class

    Tommy

    Follow @sqlscribe

Viewing 2 posts - 1 through 1 (of 1 total)

You must be logged in to reply to this topic. Login to reply