Learn more about SQL Server tools

mssqltips logo
 

Tutorials          DBA          Dev          BI          Career          Categories          Webcasts          Whitepapers          Today's Tip          Join

Tutorials      DBA      Dev      BI      Categories      Webcasts

DBA    Dev    BI    Categories

 

Retrieve Error Column in SSIS 2016


By:   |   Read Comments (10)   |   Related Tips: More > Integration Services Error Handling

Attend our free MSSQLTips Webcast - How to Simplify Routine SQL Server Administration Tasks


Problem

When there is an error in the SQL Server Integration Services (SSIS) data flow, you have the ability to redirect the error onto an error path. The system provides you with the error code and the ID of the column causing the error. However, for our logging requirements we need to have the column name as well. How can we achieve this in SSIS 2016?

Solution

In previous versions of SSIS, there was no easy way in the data flow to retrieve the name of the column that caused a component to crash. It's easy to retrieve the error description, but for the column name you only had two options:

The problem is you only get the column lineage ID and there is no easy way to retrieve which column it actually belongs. Luckily this has changed in SSIS 2016 and this tip will explain how you can fetch the column name without too much effort.

SQL Server 2016 preview

At the time of writing SQL Server 2016 is still in preview (currently CTP 2.3 has been released). This means that functionality might change, disappear or be added in future releases of SQL Server 2016. You can test the new SSIS functionality by downloading the new SQL Server Data Tools, which combines the database projects and the business intelligence projects into one single product.

Test set-up

I used a very easy package, with only one data flow:

Control flow

The data flow has the following structure:

Data flow

The source uses this query to retrieve some sample data:

SELECT Nominator = 10, Denominator = 2
UNION ALL
SELECT Nominator = 20, Denominator = 4
UNION ALL
SELECT Nominator = 30, Denominator = 6
UNION ALL
SELECT Nominator = 40, Denominator = 0
UNION ALL
SELECT Nominator = 50, Denominator = 10;

The Derived Column transformation calculates a multiplication and a division. The division will error out on row 4 because of a division by zero error.

Derived column

The Derived Column is configured to redirect errors to the error path:

Derived column error config

In previous versions of SSIS, you could see the error ID and the column ID when you ran the package:

Data viewer

Note that the data viewer also shows the error description, but this seems to be extra functionality of the data viewer since this column is not part of the error output.

Error output

During debugging, you can find the error column yourself by using the lineage ID of the column - 7 in this example - but for automated logging at the server side it would be useful if this lookup process would be simplified and automated.

Retrieving the error column in SSIS 2016

In the 2016 release of SSIS, a new function has been added to the SSIS library: GetIdentificationStringByLineageID. This function allows us to retrieve the column name by using the lineage ID. To test this, we need to add a script component as a transformation to the data flow.

Add script transformation

In the script component editor, select the ErrorCode and ErrorColumn columns as inputs.

Input columns

Create two new output columns, one for the error description and one for the column name. Make sure the width of the description column is wide enough or the script might return errors. I have chosen 5000 characters as the width in this example.

Output columns

The .NET code is pretty simple: you only need two lines of code to retrieve the description and the column name. These lines should be added in the ProcessInputRow method of your input buffer.

public override void Input0_ProcessInputRow(Input0Buffer Row)
{
    Row.ErrorDescription = this.ComponentMetaData.GetErrorDescription(Row.ErrorCode);
    Row.ErrorColumnName = this.ComponentMetaData.GetIdentificationStringByLineageID(Row.ErrorColumn);
}

When we now run the data flow, we can see the results of the script component:

Success!

Note: when you add a data viewer to the error path in SSIS 2016, the data viewer will display column name of the error column, just as the error description was shown in earlier version of SSIS. However, the column is not added to the output and is thus not logged.

Data viewer with extra columns

Conclusion

SSIS 2016 introduces a new function in its API allowing you to easily retrieve the column name of the column that caused an error in the data flow. This drastically improves the logging and the complexity of existing solutions.

Next Steps


Last Update:


signup button

next tip button



About the author
MSSQLTips author Koen Verbeeck Koen Verbeeck is a BI professional, specializing in the Microsoft BI stack with a particular love for SSIS.

View all my tips





Post a comment or let the author know this tip helped.

All comments are reviewed, so stay on subject or we may delete your comment. Note: your email address is not published. Required fields are marked with an asterisk (*).

*Name    *Email    Notify for updates 


SQL tips:

*Enter Code refresh code     



Wednesday, September 06, 2017 - 3:39:36 AM - Anders Back To Top

Hello..

I have never programmed C# before and dont know if this is the right way to do it.

But I had the same error as mentioned below.

Error: No object exists with the ID 0

 

I put in an extra line to check the row value before letting it translate it. Now it does not fail.

But I am unsure if its the way to do it or if it will ever return any results (if this check is not valid)   it might inspire someone to write a check for this :)

 

Script:

 

public class ScriptMain : UserComponent

{

    public override void Input0_ProcessInputRow(Input0Buffer Row)

    {

 

        Row.ErrorDescription = this.ComponentMetaData.GetErrorDescription(Row.ErrorCode);

 

        try

        {

            var componentMetaData130 = this.ComponentMetaData as IDTSComponentMetaData130;

            if (componentMetaData130 != null)

            {

                if(Row.ErrorColumn > 0)

                  Row.ErrorColumnDescription = componentMetaData130.GetIdentificationStringByID(Row.ErrorColumn);

                

            }

        }

        catch (Exception)

        {

            Row.ErrorColumnDescription = null;

        }

 

    }

}


Monday, July 10, 2017 - 5:00:14 AM - Koen Verbeeck Back To Top

@Imran,

what was the error?
Maybe the error is for the whole transformation and not for a specific column, hence a 0 is returned.

Regards,
Koen


Wednesday, June 21, 2017 - 7:52:15 AM - Imran Popatiya Back To Top

Hi Koen,

Yes, I am using the new code, it was working fine the day i implementated it. Below is what i have in the method. 

 Row.ErrorDescription = this.ComponentMetaData.GetErrorDescription(Row.ErrorCode);

        var componentMetaData130 = this.ComponentMetaData as IDTSComponentMetaData130;

        if (componentMetaData130 != null)

        {

            Row.ErrorColumnName = componentMetaData130.GetIdentificationStringByID(Row.ErrorColumn);

        }

I think the issue is with the ErrorColumn Id being returned as 0. Do you know when the OLE DB destination returns the ErrorColumn as 0? 

 


Wednesday, June 21, 2017 - 7:32:34 AM - Koen Verbeeck Back To Top

Imran, did you use the updated method? Microsoft changed the function during the previews.

You can find the method in the first comment (of Phil).

Koen


Wednesday, June 21, 2017 - 7:05:55 AM - Imran Popatiya Back To Top

Hi,

When i did added this script component to my package, it was working 100% fine. I was getting the Column as well as the description. 

A fews days later, today, i am running the same package with a new input CSV file and the script component breaks. It gives me the following error and exception. 

Message 1 - [SSIS.Pipeline] Error: No object exists with the ID 0.

Message 2 - [Script Component [459]] Error: System.Runtime.InteropServices.COMException (0xC0047072): Exception from HRESULT: 0xC0047072

   at Microsoft.SqlServer.Dts.Pipeline.ScriptComponentHost.HandleUserException(Exception e)

   at Microsoft.SqlServer.Dts.Pipeline.ScriptComponentHost.ProcessInput(Int32 inputID, PipelineBuffer buffer)

   at Microsoft.SqlServer.Dts.Pipeline.ManagedComponentHost.HostProcessInput(IDTSManagedComponentWrapper100 wrapper, Int32 inputID, IDTSBuffer100 pDTSBuffer, IntPtr bufferWirePacket)

 

On analysing, i found that the value for ErrorColumn is being passed as 0 to the script component and may be that is breaking it. 

I have not been able to find the reason for this, it would be great if someone can help me with it. 

 

Note - The source is a csv file and the target is a SQL table. 

 


Monday, June 05, 2017 - 5:17:56 PM - Imran Popatiya Back To Top

You saved my day!! Thank you very much!!


Wednesday, February 15, 2017 - 8:05:52 AM - Someone Back To Top

Thank you very much! You helped me many of times


Friday, February 10, 2017 - 3:03:22 PM - Steven J Neumersky Back To Top

This has long been a pet peeve of mine. We used sqlmetadata on codeplex.com to write out all SSIS objects and lineage IDs to a separate RDBMS. We would then take the error column lineage ID and do the equivalent of lookup transform to fetch the column name (in the script component we would then keep the value persisted in a sorted dictionary object in memory AFTER it was read so that we didn't need to do another read to the RDBMS if the same column ID caused the error).

I cannot believe it took this long to address.

Thanks for posting this as I had not gotten the full eval of SQL 2016 done yet.


Tuesday, July 26, 2016 - 9:56:26 AM - Koen Verbeeck Back To Top

Hi Phil,

how are you doing?

I know it has changed, it mention it in the What's new in SSIS overview here:

https://www.mssqltips.com/sqlservertip/4362/whats-new-in-sql-server-integration-services-2016--part-1/

Cheers,

Koen


Tuesday, July 26, 2016 - 8:49:32 AM - Phil Parkin Back To Top

Thanks for the article, Koen.

Note that the implementation of the method to retrieve the Error Column Name seems to have changed a little in the RTM version. Now you need to do something like this:

IDTSComponentMetaData130 componentMetaData = ComponentMetaData as IDTSComponentMetaData130;

Row.ErrorColumnName = componentMetaData.GetIdentificationStringByID(Row.ErrorColumn);


Learn more about SQL Server tools