Thursday, December 21, 2006

Adding description of error codes to an error output

When an error output of a SSIS component (ex: Lookup, Derived column, etc) is directed to another downstream components, SSIS automatically adds ErrorCode and ErrorColumn columns. The ErrorCode column of every row holds an integer values which represents an error occurred in the associated component. If you want to generate a report of failed records and send it to your colleagues, error codes may not make any sense in your report. It is always a good idea to give description which says what is the problem associated with every row. In order to get description of the error codes, we can use the method Me.ComponentMetaData.GetErrorDescription()
in a script component and append the description of error to every bad row. The
following piece of code in a script component can be used to get description of
every error and assign it to a input row column ErrorDescription.

Public Overrides Sub Input0_ProcessInputRow(ByVal Row As Input0Buffer)
Row.ErrorDescription = _
End Sub

Note: It is assumed that a Script component is attached to an error output with an output column ErrorDescription.

The following image shows a sample ssis data flow tasks