Home Dashboard Directory Help
Search

SSIS: Can't get name from ErrorColumn property in Data Flow Tasks by Anonymous8808


Status: 

Active


28
0
Sign in
to vote
Type: Bug
ID: 168857
Opened: 7/23/2006 10:32:10 PM
Access Restriction: Public
0
Workaround(s)
view
21
User(s) can reproduce this bug

Description

There is no way to programmatically derive the name of the column that produced an error in a SSIS task based on the ErrorColumn field that is added to the pipeline. The ErrorColumn contains the LineageID of the column in the originating task... but there is no way to programmatically get back to the originating task to determine the name based on the LineageID. You can open the SSIS package in Notepad and search on the LineageID (or do the same programmatically by opening the SSIS package as an XMLDocument), which is a horrendous amount of trouble to go to just to find out the name of a column... and who wants to know a LineageID?
Details
Sign in to post a comment.
Posted by allmhuran on 6/16/2014 at 10:15 PM
When was voting disabled for this item? It is truly mind boggling that this problem still exists.
Posted by David Daiker on 10/10/2013 at 12:15 PM
After 8 years of SSIS we still don't have a good way to handle errors in data flows. We've resorted to using the SSIS Data Flow Discoverer solution found on CodePlex but it's a lot of work to do in each of our packages just to know what column failed. Layer on how much work it is to know which row(s) failed and half of our packages is error handling and there are ugly red lines running all over the place.

On top of that you often don't get the "inner" exceptions that happen during DB operations when you redirect errors. So now we have to choose between knowing what data failed and what the error is.
Posted by Alexander Suprun on 6/3/2013 at 10:24 AM
The issue became even worse in SSIS 2012, because LineageID has been removed from the package definition, but ErrorColumn is still pointing to LineageID. What can we do with this useless ErrorColumn?
Posted by Doug_Ivison on 9/1/2010 at 9:35 AM
What if an easy fix becomes possible, by looking at the problem differently?


Right now, the column number passed in ErrorColumn is the ID (and LineageID--same number) from Source Output.
How hard would it be to simply pass the LineageID from SOURCE ERROR OUTPUT, instead of Source Output?
Or pass both (add a column)?

Why: the column's LineageID from the source component's "Source Error Output" output columns
MATCHES that column's LineageID in the script component's "Input 0" input columns.

So, if the "source ERROR output" lineageid is passed, it can be matched to the SCRIPT COMPONENT's own metadata... already accessible.



So, two possible enhancements that would handle this:

At development-time, provide either:
(1) a column OPTION to choose WHICH lineage ID is passed is ErrorColumn,
or (2) a new column (ErrOutColLineageID perhaps), that would contain LineageID from Source Error Output.


Example from my situation:
in BIDS / VS 2005, right-clicking on my source object (an Excel Source),
and selecting "Show Advanced Editor", then tab "Input and Output Properties",
then expanding "Excel Source Output", then expanding "Output Columns",
selecting column "F11" reveals that the ID and LineageID are both 15316.

15316 matches the content of column "ErrorColumn"...
but it's difficult at best to use that

On the other hand,
in BIDS / VS 2005, right-clicking on my source object (an Excel Source),
and selecting "Show Advanced Editor", then tab "Input and Output Properties",
then expanding "Excel Source ERROR Output", (caps mine, for emphasis),
then expanding "Output Columns",
selecting column "F11" reveals an ID and LineageID (again, same) of 15317.

And finally,
in BIDS / VS 2005, right-clicking the script component,
(which is connected to the red arrow from my source component :),
and selecting "Show Advanced Editor", then tab "Input and Output Properties",
then expanding "Input 0,
then expanding "Input Columns",
selecting column "F11" reveals an ID of 22687, and LineageID of 15317.


So, if either ErrorColumn contained 15317 (instead of 15316),
or a new column (ErrOutColLineageID?) contained 15317,
then we could at least interrogate the script component's own metadata
to match the number and lookup the name.

Then, column name retrieval could be as simple as:

        Dim inputCol As IDTSInputColumn90
        Try
            For Each inputCol In ComponentMetaData.InputCollection(0).InputColumnCollection
                If inputCol.LineageID = Row.ErrOutColLineageID Then
                    Row.ErrColumnName = inputCol.Name
                End If
            Next
        Catch
            Row.ErrColumnName = "Column name retrieval #2 failed (" & ex.Message.Trim & ")."
        End Try



Or the line...
                If inputCol.LineageID = Row.ErrOutColLineageID Then
might be...
                If inputCol.LineageID = Row.ErrorColumn Then
... if the fix was a COLUMN OPTION, for ErrorColumn to contain LineageID from source error output.
Posted by Microsoft on 7/24/2006 at 9:56 AM
Thank you for using SQL Server 2005. This is a good suggestion and we are investigating for a future release. We are reviewing various ways in which users can further extend and customize SSIS.
Thank you.
Sign in to post a workaround.