Published on

December 24, 2008

Improving SSIS Error Handling in SQL Server

Anyone who has used SSIS error handling has likely been frustrated by the lack of descriptive error messages. When an error occurs, SSIS returns an error code with no accompanying description, making it difficult to troubleshoot and fix the issue. However, there are ways to enhance the error handling process in SQL Server Integration Services (SSIS) to provide more meaningful error information.

One solution to this problem is to use a Script Component that retrieves the error description. This can be achieved by leveraging the Input0_ProcessInputRow method and reflection, as demonstrated in a blog post by Jamie Thomson. This method allows you to loop through the columns, build an XML string with name-value pairs, and insert it into a table for further analysis.

To implement this solution, you need to create a table in your SQL Server database to store the error information. Here is an example of how to create the table:

CREATE TABLE [data_xfer].[load_errors]
(
    [load_error_id] [int] IDENTITY(1,1) NOT NULL,
    [package_run_date] [smalldatetime] NULL CONSTRAINT [DF_load_errors_error_date] DEFAULT (getdate()),
    [error_task] [varchar](100) NULL,
    [error_step] [varchar](100) NULL,
    [error_code] [int] NULL,
    [error_desc] [varchar](100) NULL,
    [error_details] [xml] NULL,
    CONSTRAINT [PK_load_errors] PRIMARY KEY NONCLUSTERED
    (
        [load_error_id] ASC
    ) WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

Next, you need to configure the error output in your SSIS package by adding a script component. Follow these steps:

  1. On the Input screen, add the columns that could be the source of the error.
  2. On the Inputs and Outputs screen, add the following columns to the Output:
    • ErrorStep string(100) – This is the name of the Script Component, which can be set using the step that is the source of the error.
    • ErrorDataDetails Unicode String(4000) – This is an XML string that will contain the data from the input columns and will be inserted into an XML Type column in SQL Server.
    • ErrorDesc string(100) – The error description obtained from the ErrorCode.
    • ErrorTask string(100) – The SSIS Task that the Step is in. This can be retrieved using the TaskName package variable created by SSIS.
    • PackageTime Date – The start time of the package. This can be retrieved using the StartTime package variable.
  3. On the Script screen, add the following to the ReadOnlyVariables:
    • TaskName – A default SSIS variable that contains the task name.
    • StartTime – A default SSIS variable that represents the start time of the package.

With the setup complete, you can now design the script in Visual Studio for Applications. Here is an example script that can be used:

Imports System
Imports System.Data
Imports System.Math
Imports Microsoft.SqlServer.Dts.Pipeline.Wrapper
Imports Microsoft.SqlServer.Dts.Runtime.Wrapper
' added this import
Imports System.Reflection

Public Class ScriptMain
    Inherits UserComponent

    Public Overrides Sub Input0_ProcessInputRow(ByVal Row As Input0Buffer)
        Dim column As IDTSInputColumn90
        Dim rowType As Type = Row.GetType()
        Dim columnValue As PropertyInfo
        Dim strErrorDetails As String = "<fields><field "
        Dim strAttributeName As String
        Dim strValue As String

        ' Get the error description
        Row.ErrorDesc = ComponentMetaData.GetErrorDescription(Row.ErrorCode).Trim()

        ' This actually gets the script component name
        Row.ErrorSource = ComponentMetaData.Name.Trim()

        ' Get the task name and the package start time
        Row.ErrorTask = Variables.TaskName.Trim()
        Row.PackageTime = Variables.StartTime

        Try
            ' Loop through the columns in the input to create a generic XML doc
            For Each column In ComponentMetaData.InputCollection(0).InputColumnCollection
                ' Remove illegal XML characters in the name
                If column.Name.IndexOf(".") > -1 Then
                    strAttributeName = column.Name.Replace(".", "")
                Else
                    strAttributeName = column.Name
                End If

                If strAttributeName.IndexOf("_") > -1 Then
                    strAttributeName = strAttributeName.Replace("_", "")
                End If

                If strAttributeName.IndexOf(" ") > -1 Then
                    strAttributeName = strAttributeName.Replace(" ", "")
                End If

                ' Skip the error columns
                If Not (strAttributeName.Contains("ErrorCode") Or strAttributeName.Contains("ErrorColumn")) Then
                    ' Get the value for the column
                    columnValue = rowType.GetProperty(strAttributeName)
                    Dim objColumn As Object = columnValue.GetValue(Row, Nothing)

                    ' If the value is null, set it to an empty string
                    If IsNothing(objColumn) Then
                        strValue = String.Empty
                    Else
                        strValue = objColumn.ToString()
                    End If

                    ' Append the node to the XML string
                    strErrorDetails = strErrorDetails & "name=" & ControlChars.Quote & column.Name.Trim & ControlChars.Quote & " value=" & ControlChars.Quote & strValue & ControlChars.Quote & " /><field "
                End If
            Next

            ' Finish the XML string
            strErrorDetails = strErrorDetails.Substring(0, strErrorDetails.Length - 7) & "</fields>"
        Catch ex As Exception
            ' If an error occurs, log it and keep processing instead of halting the load
            strErrorDetails = "<fields><field name=" & ControlChars.Quote & "error" & ControlChars.Quote & " value=" & ControlChars.Quote & ex.ToString.Trim & " " & strAttributeName.Trim & ControlChars.Quote & "/></fields>"
        End Try

        ' Add the XML output to the output row
        Row.ErrorDetails = strErrorDetails
    End Sub
End Class

One way to utilize this error data is to verify the successful loading of duplicate records. For example, if you have duplicate records for a person in your source system, you can assign one of the person numbers as the primary number for the transfer. When loading the addresses for this person, you may encounter errors due to data integrity rules. By querying the load_errors table, you can confirm that each address for the person was loaded only once.

Here is an example of how you can query the error data:

SELECT
    L.error_details,
    L.package_run_date,
    L.error_task,
    L.error_step,
    L.error_code,
    L.error_desc,
    P.person.value('@value', 'char(6)') AS error_person_id
FROM
    data_xfer.load_errors L
CROSS APPLY
    error_details.nodes('.//field[@name="personno"]') AS P(person)

It is important to note that specific queries need to be written based on the task that created the error, as the XML generated is a generic name-value document and the names may vary.

While the code provided is not original and was found from the referenced resources, it offers a simple solution for logging errors and associated data. There are areas where it can be improved, such as handling binary data in the row and stripping illegal characters from the XML. Any comments or suggestions on how to enhance the process are welcome.

References:

Click to rate this post!
[Total: 0 Average: 0]

Let's work together

Send us a message or book free introductory meeting with us using button below.