Since SQL Server 2016 there is a ‘built-in’ function to retrieve the actual column name of the column which caused the error. But there’s also a flaw, in case of a database constraint the ‘ErrorColumn’ (input parameter) will remain empty. So I’ve added an ELSE-statement to it in case it doesn’t receive a value:
How to set it up
1) Copy the script below
public override void Input0_ProcessInputRow(Input0Buffer Row)
Row.ErrorDescription = this.ComponentMetaData.GetErrorDescription(Row.ErrorCode);
IDTSComponentMetaData130 componentMetaData = this.ComponentMetaData as IDTSComponentMetaData130;
if (Row.ErrorColumn > 0)
Row.ErrorColumnName = componentMetaData.GetIdentificationStringByID(Row.ErrorColumn);
Row.ErrorColumnName = "Database constraint :)";
2) Paste it into the script window and save it
3) Select both ‘ErrorCode’ and ‘ErrorColumn’ as input parameters
4) Now add two new output columns as shown below
In my case both output column are of data type string (DT_STR) with a size 250.
5) Add a Derived Column to the Data Flow after the Script Component and use the following SSIS expression to replace the original output value of ‘ErrorColumnName’
REPLACE( REVERSE( SUBSTRING( REVERSE( ErrorColumnName ),1,FINDSTRING( REVERSE(ErrorColumnName) ,”[“,1) – 1) ),”]”,” “)
(The original output value also contains the component’s name and we’re just interested in the column name itself)
There you go!
Many thanks to my colleague Gigi for the SSIS Expression 🙂
Grrr…this one can be annoying!
But when you know how to cope with it, it’s an easy fix.
The problem: The SSIS package is failing validation with a message to the effect of “The package contains two objects with the duplicate name of “output column “ErrorCode” (123)” and “output column “ErrorCode” (456)”.
The explanation: The Data Flow source (for at least OLE DB Sources) will have a Error Output that by default will contain all the columns of the Source with two additional columns: ErrorCode and ErrorColumn.
The solution: The source column can be renamed as a last resort. This is often not practical or possible, the better solution is to rename the Error Output column. With the data flow open:
1) Choose the Data Flow Source
2) Choose Properties
3) Click ‘Show Advanced Editor’
4) Navigate to ‘Input and Output Properties’
5) Open the Source Error Output
6) Scroll to the bottom of the list and rename the last ‘ErrorCode’ column.
Thanks to: http://bturnip.com/nerdalerts/archives/146
Here’s another annoying thing that might happen when a stored procedure is too ‘complex’ for SSIS:
"The metadata could not be determined because every code path results in an error..."
"Unable to retrieve column information from the data source. Make sure your target table in the database is available"
Simply said, it needs help regarding the metadata.
One way to do this is to provide SSIS the necessary metadata information. As shown below:
Microsoft SQL Server 2012 extends the EXECUTE statement to introduce WITH RESULT SETS option which can be used to change the Column Name and Data Types of the result set returned by the execution of stored procedure.
Example Using WITH RESULT SETS Feature of SQL Server 2012
Example - Using WITH RESULT SETS Feature of SQL Server 2012
WITH RESULT SETS
[Employe Name] NVARCHAR(100),
[Employee City] NVARCHAR(20),
[Employee Postal Code] NVARCHAR(30)
The WITH RESULT SET Feature of SQL Server 2012 is a great enhancement to the EXECUTE Statement. This feature will be widely used by Business Intelligence Developers to execute a stored procedure with in an SQL Server Integration Services (SSIS) Package to return the result set with required Columns and modified data types.
Read more: http://www.mytechmantra.com/LearnSQLServer/With-Result-Set-Feature-SQL-Server-2012.html#ixzz3nybyEcJU