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.
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 
The solution is must simpler now for SQL Server 2016 as explained in https://technet.microsoft.com/en-us/library/ms345163%28v=sql.130%29.aspx. A simple line in a script is enough.
Using the new approach, the SQL destination component returns me ErrorColumn as 0. Does someone has any idea why is this so and what is the solution for this. I really need to know which column is causing the issue. Thanks@
This worked for the Error Column Name in SSIS 2012 and it was really easy to implement. Thanks a bunch Benny!
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
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?
Run the compiled exe from command prompt. DFLD is a console application.
That worked….doh!!! Thanks Benny…you’re awesome.
Have you tried to implement this in SSIS 2012. If so what are the required steps.
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.
This is very useful thanks a lot….
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
Thanks for your feedback Ryan. Much appreciated. I will address this in the next version of DFLD