SSIS: How to get name of ErrorColumn from Error Output?

SSIS automatically creates two columns on the Error Output from dataflow components – ErrorColumn and ErrorCode. ErrorColumn contains the column ID of the erroneous column. Column ID is a number that is unique to a dataflow within the package. Similarly ErrorCode contains the error number that caused the row to be rejected. At the moment, SSIS by default does not provide ErrorColumn name and ErrorCode description. These can be derived using the methods described below.

Getting ErrorCode Description

Getting description of ErrorCode is relatively straight forward. This MSDN article describes how to get the description of ErrorCode using a script component http://technet.microsoft.com/en-us/library/ms345163.aspx. Just override the ProcessInputRow() with the c# code given below:

public override void Input0_ProcessInputRow(Input0Buffer Row)
    {
  Row.ErrorDesc = this.ComponentMetaData.GetErrorDescription(Row.ErrorCode);
    }

 

Getting ErrorColumn Name

Getting the name of ErrorColumn is not as straightforward as getting the description of ErrorCode. This MSDN article describes how it can be done using Integration Service APIs to capture dataflow column metadata to a lookup table and then using the lookup table to get the name of the ErrorColumn. The utility to capture dataflow column metadata is Dataflow Discoverer (DFLD).

DFLD is a command-line discovery utility that detects and writes the metadata of SSIS dataflow columns to a SQL Server lookup table. DFLD detects Dataflows nested within containers up to any level of nesting. For information on how to install and use DFLD utility check this out http://dfld.codeplex.com/

After you execute DFLD, the dataflow column metadata is available in the lookup table DFLMetadata. Union all the error outputs and perform a lookup on DFLMetadata to get the name of the ErrorColumn as shown in Screen Capture 1.

Screen Capture 1 - Dataflow using metadata captured by DFLD to lookup name of ErrorColumn

The key columns for lookup on DFLMetadata are

• PackageID – which is available from the system variable @[System::PackageID]
• DataflowID – which is available from the system variable @[System::TaskID]
• ErrorColumn – which is the column ID available from Error Output

Apart from name of ErrorColumn, the DFLD utility also generates the following information about dataflow columns

Metadata	Data Type	Description
PackageID	Varchar(38)	Unique GUID of the package
PackageName	Varchar(200)	Recommended to keep package name under 200 characters
DataflowID	Varchar(38)	GUID of the Dataflow
DataflowName	Varchar(200)	Recommended to keep dataflow name under 200 characters
IOType	Char(1)	Flag to indicate whether the collection is Input or Output. I= Input Collection, O= Output Collection
IOName	Varchar(200)	Name of Input/output collection
IOID	Int	ID of Input/output collection
ColumnName	Varchar(200)	Recommended to keep Column name under 200 characters
ColumnDesc	Varchar(500)
LineageID	Int	It’s an integer value which identifies the column in the buffer
ColumnID	Int	Unique value that identifies a column within a Dataflow in the package.
ColumnDataType	Varchar(30)
ColumnPrecision	Int
ColumnScale	Int
UpstreamComponentName	Varchar(200)
SortKeyPosition	Int
ColumnCodePage	Int
MappedColumnID	Int
UsageType	Int
ColumnType	Int

 

Note on ErrorCode Description and ErrorColumn Name for Dataflow Destinations

ErrorCode and ErrorColumn generated from Dataflow destinations differ from all other Dataflow components – something to be aware of:

• Integration Services does not provide description of ErrorCode from the Error Output of destination component.
• Integration Services return ErrorColumn=0 for failures at destination component, so it is not possible to resolve the name of ErrorColumn from metadata.

Benny Austin

RT @bennyaustin: New codeplex project – Dataflow Discoverer is out now http://dfld.codeplex.com/ #msbi—
Russell Christopher (@russch) February 05, 2011

About these ads