Retrieve the column causing an error in SQL Server Integration Services

By:   |   Comments (16)   |   Related: More > Integration Services Error Handling


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?


In versions of SSIS before SQL Server 2016, there was no easy way in the data flow to retrieve the name of the column that caused a component to fail. It's easy to retrieve the error description, but for the name of the specific column 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. The solution provided in this tip is valid for SSIS 2016 and later versions.

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
SELECT Nominator = 20, Denominator = 4
SELECT Nominator = 30, Denominator = 6
SELECT Nominator = 40, Denominator = 0
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 or later

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 three 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);
    IDTSComponentMetaData130 componentMetaData = this.ComponentMetaData as IDTSComponentMetaData130;
    Row.ErrorColumnName = componentMetaData.GetIdentificationStringByID(Row.ErrorColumn);

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


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

Data viewer with extra columns


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 improves the logging capabilities of SSIS and it reduces the complexity of existing solutions.

Next Steps

sql server categories

sql server webinars

subscribe to mssqltips

sql server tutorials

sql server white papers

next tip

About the author
MSSQLTips author Koen Verbeeck Koen Verbeeck is a seasoned business intelligence consultant at AE. He has over a decade of experience with the Microsoft Data Platform in numerous industries. He holds several certifications and is a prolific writer contributing content about SSIS, ADF, SSAS, SSRS, MDS, Power BI, Snowflake and Azure services. He has spoken at PASS, SQLBits, dataMinds Connect and delivers webinars on Koen has been awarded the Microsoft MVP data platform award for many years.

This author pledges the content of this article is based on professional experience and not AI generated.

View all my tips

Comments For This Article

Tuesday, March 14, 2023 - 3:11:55 AM - Koen Verbeeck Back To Top (91008)
Hi Luther,

no, I don't have any experience doing this with custom error handlers. I'm also not a big fan of those, as they "hide" logic from the developer (it's not always very visible that there are some tasks who do extra work).


Monday, March 13, 2023 - 1:18:09 PM - Luther Atkinson Back To Top (91004)
Have you tried incorporating this into a custom error handler instead of keeping it in the dataflow? If you have, I'd like to see some tips on how to set it up.

Monday, December 28, 2020 - 2:54:29 AM - person Back To Top (87957)
Thank you so much!!!!!!!

Friday, October 30, 2020 - 1:06:57 PM - Boris Back To Top (86729)
This is super useful! Thank you!

Wednesday, June 27, 2018 - 2:53:04 AM - Koen Verbeeck Back To Top (76430)

Hi Hari Chandan Uppu,

as stated in the article, you need to select the "transformation" as type for the script component.
Hope this helps.


Tuesday, June 26, 2018 - 3:01:39 PM - Hari Chandan Uppu Back To Top (76423)

Please let me know when I am selecting script component it is asking for select source destination and transform data among all 3 which one I have to select for while creating 

Wednesday, September 6, 2017 - 3:39:36 AM - Anders Back To Top (65926)


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 :)




public class ScriptMain : UserComponent


    public override void Input0_ProcessInputRow(Input0Buffer Row)



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




            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 (59178)


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


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

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 (57856)

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


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


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 5, 2017 - 5:17:56 PM - Imran Popatiya Back To Top (56797)

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

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

Thank you very much! You helped me many of times

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

This has long been a pet peeve of mine. We used sqlmetadata on 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 (42975)

Hi Phil,

how are you doing?

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



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

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);

get free sql tips
agree to terms