SSIS - Error Output .. Column Disappeared

  • Hi All,

    I am testing a SSIS package and from a Derived Column Transformation task I am trying to create and error output of the corrupt data to a flat file for a partiicular column.

    The issue is that the column I require is no longer on the output stream for available columns.

    There are two other columns: errorcode and errorcolumn.

    Could you tell me how I can just extract the corrupt data to the flat file for that column only please.

    Thanks,

  • Having looked at it a bit more the issue seems to be any new columns added to the Derived Component is not being added to the Data-Path of the Error output stream.

    Is there anyway to add the new columns so that I can extract the corrupt data to the Flat File Destination?

  • The source is Oracle 9i

    Looks like I cannot get any error output on any columns which I have 'derived' but I can get out put on the columns where no transformations have been made.

    (sorry for the constant updates) but any help would be appreciated.

  • aarionsql (4/17/2012)


    Having looked at it a bit more the issue seems to be any new columns added to the Derived Component is not being added to the Data-Path of the Error output stream.

    Is there anyway to add the new columns so that I can extract the corrupt data to the Flat File Destination?

    Columns created in the derived column are not added to the error output directly as it makes no sense. If the formula fails, the column would be empty anyways.

    You need to monitor the individual columns used in your derived column expression.

    Need an answer? No, you need a question
    My blog at https://sqlkover.com.
    MCSE Business Intelligence - Microsoft Data Platform MVP

  • Thank you for your reply. The column is a concatenation of an ID and Date field and there is a truncation error which is stopping the package from running.

    Is there any way of extracting the data which may be creating this error?

  • aarionsql (4/18/2012)


    Thank you for your reply. The column is a concatenation of an ID and Date field and there is a truncation error which is stopping the package from running.

    Is there any way of extracting the data which may be creating this error?

    Is it for debugging only, or does it need to be a permanent solution?

    If it's temporary, you could put a conditional split before the derived column that adds the two columns, checks the length and sends every row with a length greater than the derived column length to another path.

    Need an answer? No, you need a question
    My blog at https://sqlkover.com.
    MCSE Business Intelligence - Microsoft Data Platform MVP

  • It's is for debugging only.

    I will try the conditional split .. The conditions on the split will be looking for the lenght ... I would have never thought if that .. Many thanks 🙂

  • aarionsql (4/18/2012)


    It's is for debugging only.

    I will try the conditional split .. The conditions on the split will be looking for the lenght ... I would have never thought if that .. Many thanks 🙂

    No problem, glad to help.

    Need an answer? No, you need a question
    My blog at https://sqlkover.com.
    MCSE Business Intelligence - Microsoft Data Platform MVP

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply