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:
- On the Input screen, add the columns that could be the source of the error.
- 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.
- 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:
- SSIS Junkie – SSIS Nugget: Get Error Descriptions
- BI Thoughts and Theories – Address Columns Generically in a Script Component
- Jack Corbett – www.ntm.org/jack_corbett