It’s a common frustration with Error Outputs in Integration Services that you get numeric identifiers for the error that occurred and the column that caused it, but you don’t automatically get the description of the error or the name of the column.
It’s easy to add the description of the error with 1 line of code in a Script component, as described in this BOL topic: Enhancing an Error Output with the Script Component.
It’s not so easy to add the name of the column.
(Note: In some cases, ErrorColumn = 0 when the error is deemed to affect the whole row and not a single column – for example, in the case of a failed Lookup.)
I’ve uploaded a sample that demonstrates a rather simplistic and “brute force”, but effective, approach: Create a reference table of Column IDs ahead of time (by running a small standalone application for this purpose), then simply perform a Lookup inside the package to add ErrorColumnName to the error output in the data flow.
Column IDs are unique within the containing Data Flow task, so your reference table requires at least these columns:
- PackageID
- DataFlowTaskID
- ColumnID
- ColumnName
The package uses the following components to add the 2 descriptive columns to the data flow. There are a few ways that you could rearrange or consolidate this, but I chose to make each step explicit.
- Script component to add the Error Description. Connect this component to the Error Output. Add the new ErrorDescription output column and populate it with a line of code that calls:
Row.ErrorDescription = Me.ComponentMetaData.GetErrorDescription(Row.ErrorCode)
Row.ErrorDescription = this.ComponentMetaData.GetErrorDescription(Row.ErrorCode);
- Derived Column to add the system variables for PackageID and TaskID (the containing Data Flow task) to the data flow, for use in the Lookup of the column name.
- Derived Column to add the new ErrorColumnName output column. Where ErrorColumn = 0, populate the ErrorColumnName column with some informative string like “[Not a column-specific error.]”
- Conditional Split to split row-level errors from column-level errors. Check for ErrorColumn = 0 and send those rows out a separate output.
- Lookup on the column-error output to look up the column name in the reference table by using PackageID, DataFlowTaskID, and ErrorColumn as parameters.
- Union All to recombine the row-error and column-error paths
.
And finally, you have your error description and error column name, as seen here in the data viewer!
