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
Thanks Benny, this worked for me! FYI, when I configured my Derived Columns, for PackageID and DataflowID, and added the corresponding System variables, the Data Type for each automatically was defined as “Unicode string [DT_WSTR]” which later caused a ‘Type Mismatch” error when matching up the PackageID and DataflowID columns in my Lookup. As you might guess, this was because the data types in the database for these fields were varchar, not nvarchar. Switching these columns to nvarchar(38) fixed the problem. Hope this helps someone and thanks again.
-Ryan
Posted by Ryan S. | January 28, 2012, 07:34Thanks for your feedback Ryan. Much appreciated. I will address this in the next version of DFLD
Posted by Benny Austin | January 28, 2012, 20:29