none
SSIS Error Column Name RRS feed

  • Question

  • Hi

             How do i get Error column Name in SSIS . The  Script component has the error row which is passed as parameter to the pipeline.I want to get the exact column name for which the error had ocurred

     

    This does not work with

    Row.ErrorDescription =ComponentMetadata.GetErrorDescription(Row.ErrorColumn)

     

    Plz Help

    Thanks in advance

    anlis

     

     

    Friday, July 4, 2008 11:45 AM

Answers

  • BOL ( http://technet.microsoft.com/en-us/library/aa337088.aspx ) offers this suggestion:

     

    Add the name of the error column. Looking up the column name that corresponds to the column ID saved by the error output cannot easily be done in the Script component and requires additional steps. Each column ID in a data flow is unique within that Data Flow task, and is persisted in the package at design time. The following approach is one suggestion for adding the column name to the error output:

    1. Create a lookup table of column names. Create a separate application that uses the Integration Services API to iterate over each saved package, each data flow in the package, each object in the data flow, and each input and output in the data flow object. The application should persist the column ID and name of each column to a lookup table, along with the ID of the parent Data Flow task and the ID of the package.
    2. Add the column name to the output. Add a Lookup transformation to the error output that looks up the column name in the lookup table created in the preceding step. The lookup can use the column ID in the error output, the package ID (available in the system variable System:Stick out tongueackageID), and the ID of the Data Flow task (available in the system variable System::TaskID).

     

    SQL Server MVP Simon Sabin has also created a custom component that does this work for you in the data flow:

     

    http://sqlblogcasts.com/files/3/transforms/entry2.aspx

     

    I also think I've seen a few other approaches to this over the years, but can't seem to find them online today...

     

    Friday, July 4, 2008 2:13 PM
    Moderator

All replies

  • BOL ( http://technet.microsoft.com/en-us/library/aa337088.aspx ) offers this suggestion:

     

    Add the name of the error column. Looking up the column name that corresponds to the column ID saved by the error output cannot easily be done in the Script component and requires additional steps. Each column ID in a data flow is unique within that Data Flow task, and is persisted in the package at design time. The following approach is one suggestion for adding the column name to the error output:

    1. Create a lookup table of column names. Create a separate application that uses the Integration Services API to iterate over each saved package, each data flow in the package, each object in the data flow, and each input and output in the data flow object. The application should persist the column ID and name of each column to a lookup table, along with the ID of the parent Data Flow task and the ID of the package.
    2. Add the column name to the output. Add a Lookup transformation to the error output that looks up the column name in the lookup table created in the preceding step. The lookup can use the column ID in the error output, the package ID (available in the system variable System:Stick out tongueackageID), and the ID of the Data Flow task (available in the system variable System::TaskID).

     

    SQL Server MVP Simon Sabin has also created a custom component that does this work for you in the data flow:

     

    http://sqlblogcasts.com/files/3/transforms/entry2.aspx

     

    I also think I've seen a few other approaches to this over the years, but can't seem to find them online today...

     

    Friday, July 4, 2008 2:13 PM
    Moderator
  • hi

    This was not what i was trying for .

    I am trying to get error column name without adding any additional etl tasks .If i try those lookups and all other things that u have mentioned i would face  performance  issues which i don't want

     

     

    Thanks for all ur information

    anlis

     

    Saturday, July 5, 2008 12:04 PM
  • You can download this component which retrieve error column name and error description : http://eod.codeplex.com/
    Source Code is available so you can adapt it if you want.

    Thursday, September 3, 2009 2:10 PM