Uncategorized


Whenever we develop data maintenance packages through SSIS for our customers, one of the areas we pay particular attention to are invalid records. Where possible, we work with the owner of the data to put in code to cleanse the data as it is processed, but for unexpected errors, we like to create customer logging and error trapping, which processes the valid data, logs the invalid data (for manual entry or correction), and continues without failing the who job.

Microsoft SQL Server Integration Studio (SSIS) provides a flexible logging mechanism that is tightly integrated with the various events raised during package execution. A simple, straight-forward custom logging schema can provide the information for locating problems during a run cycle.

CREATE TABLE [SSIS_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]

Now, to use the table, we then redirect any errors processed within the data processing element of the SSIS process. By using the Input0_ProcessInputRow method and reflection we then loop through the columns, building an XML string (name-value pairs) which we can then insert into the new SSIS error logging table.

Clearly, we need to hold and set variables (as shown in the script below) for setting the package name, package start date (so we get different errors per run) of the SSIS.

Finally, we can create a nice routine at the end of the regular SSIS run, which sweeps the new error table for errors, turns it into an HTML form email, and send an emailed report of the errors to the SSIS administrator.

The error trapping script which we insert into the SQL Server error event is as follows:

ImportsSystem
ImportsSystem.Data
ImportsSystem.Math
ImportsMicrosoft.SqlServer.Dts.Pipeline.Wrapper
ImportsMicrosoft.SqlServer.Dts.Runtime.Wrapper
‘ added this import
ImportsSystem.Reflection
PublicClass 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 = ” -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
IfNot (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 to 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 & _
” />
End If
Next
‘finish the xml string
strErrorDetails = strErrorDetails.Substring(0, strErrorDetails.Length – 7) & _
“”
Catch ex As Exception
‘ if an error occurs log it and keep processing instead of killing the load
strErrorDetails = “”
End Try
‘ add the xml output to the output row
Row.ErrorDetails = strErrorDetails
End Sub
EndClass

ImportsSystem
ImportsSystem.Data
ImportsSystem.Math
ImportsMicrosoft.SqlServer.Dts.Pipeline.Wrapper
ImportsMicrosoft.SqlServer.Dts.Runtime.Wrapper
‘ added this import
ImportsSystem.Reflection
PublicClass 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 = ” -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
IfNot (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 to 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 & _
” />
End If
Next
‘finish the xml string
strErrorDetails = strErrorDetails.Substring(0, strErrorDetails.Length – 7) & _
“”
Catch ex As Exception
‘ if an error occurs log it and keep processing instead of killing the load
strErrorDetails = “”
End Try
‘ add the xml output to the output row
Row.ErrorDetails = strErrorDetails
End Sub
EndClass


Uncategorized

SSIS Error trapping to log files – the Touchstone way (better than failing everything)

Whenever we develop data maintenance packages through SSIS for our customers, one of the areas we pay particular attention to are invalid records. Where possible, we work with the owner of the data to put in code to

Uncategorized

SSIS Error trapping to log files – the Touchstone way (better than failing everything)

Whenever we develop data maintenance packages through SSIS for our customers, one of the areas we pay particular attention to are invalid records. Where possible, we work with the owner of the data to put in code to