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.


18 comments

  1. Hi Benny,

    I am using VS and SQL Server 2012 and have been able to get the DFLD executable working on my SSIS Development environment. I am deploying my SSIS project to the Integration Services Catalog for execution on my Production environment. Seeing as how the DFLD executable requires the package file path as an input, how would I go about running the executable on my Production environment when the packages are stored in the Integration Services Catalog?

    Thanks.

    • Mark, at the moment DFLD can extract SSIS metadata only from file path. Future releases might include this option to extract from SSIS catalog. For now, you might have to point the metadata database to production or export the metadata from an existing environment.

      • I used the below bcp command to generate the .dtsx files from msdb, then loop thorugh all of them on a daily basis to refresh the meta data and use DFLD to push package information

        bcp “select cast(cast(packagedata as varbinary(max)) as varchar(max)) from MSDBrestore.dbo.sysssispackages where name = ‘MIPODS'” queryout c:\temp\PackageNameMIPODS.dtsx -Slocalhost -T -c

  2. Hi Benny,

    Using SSIS 2012. I removed all references > readded them again. Built the sln with no issue. As soon as I debug it in VS 2012, console window opens and closes immediately…nothing else happens. What might be the issue?

    • Jason, DFLD does work for SSIS 2012 packages as it is. One thing I did notice was that if there are multiple versions of SQL Server on the same server, then you have to edit the path environment variable and re-arrange SQL Server 2012 paths (\Microsoft SQL Server\110) to be ahead of earlier versions like SQL 2008 (\Microsoft SQL Server\100). And also the 64-bit path must be ahead of the x86 path, if you are using a 64 bit version. In this way the correct version of SSIS Application will be executed by DFLD. Otherwise you might get the following exception

      “The package failed to load due to error 0xC0011008 “Error loading from XML. No further detailed error information can be specified for this problem because no Events object was passed where detailed error information can be stored.”. This occurs when CPackage::LoadFromXML fails.”

      • We have a fresh install of sqlserver 2012 and ssis 2012(no prior versions). I was getting errors trying to run the dfld executable. I did get it to work today though. I downloaded the source. Then I had to remove the references in visual studio and reattach them. It seems it was looking for an old version somewhere. But it works now. Thanks you.

  3. 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

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s