SSIS - Flat file data error

  • I currently have an SSIS process that just inputs data from a CSV file as is and treats it as the source of truth.

    What we have noticed is sometimes we receive a character(s) A-Z as well which we have been told should not be imported as its an error with the export. Since the export process can not change it has been left up to SSIS to move this error record into the new table for review.

    Basically the process is as follows.

    1. Simple Data flow Flat file task

    2. Flat file row (is successful) goes into the correct table

    3. Flat file row (if contains a character) goes into the error table

    The first column is an INT and any VARCHAR fails validation moves it to the error table.

    The problem I have is, I want the whole row to be moved column by column. The table is an exact replica except labelled Error. However I can only see 3 options "Flat File Source Error Output Column", "Error Code", "Error Column" These 3 values do not show me which column was the problem, If that makes sense?

    ID|Name|

    1|JS

    2|JohnSmith

    3A|John

    3|JSmith

    I would like 3A and John to be in my error table as 3A | John so I can see all the data.

    Hope the above helps.

  • Would it not be a better option instead be to treat all columns coming in at the source as strings and then use a Data Conversion Transformation Note and try and convert your ID field to an Integer. Any successful rows can be routed on as normal, and the ID is converted to an integer, while any failed rows can be passed to a different table in the failed dataflow.

    Thom~

    Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
    Larnu.uk

  • Thom A (7/6/2016)


    Would it not be a better option instead be to treat all columns coming in at the source as strings and then use a Data Conversion Transformation Note and try and convert your ID field to an Integer. Any successful rows can be routed on as normal, and the ID is converted to an integer, while any failed rows can be passed to a different table in the failed dataflow.

    Quiet possibly, never thought about that - just thought to do it all in one go at the start.

    I've never used the Data Conv. Trans in SSIS so i'll give it a go and revert if any issues.

  • Tava (7/6/2016)


    Thom A (7/6/2016)


    Would it not be a better option instead be to treat all columns coming in at the source as strings and then use a Data Conversion Transformation Note and try and convert your ID field to an Integer. Any successful rows can be routed on as normal, and the ID is converted to an integer, while any failed rows can be passed to a different table in the failed dataflow.

    Quiet possibly, never thought about that - just thought to do it all in one go at the start.

    I've never used the Data Conv. Trans in SSIS so i'll give it a go and revert if any issues.

    This process worked well, no issues with it what so ever. I'll use it going forward... thanks for your help

  • Tava (7/6/2016)


    Since the export process can not change...

    It comes from a computer. It can be changed. It's ok to do a temporary work around but they need to fix it at the source.

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.


    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • Jeff Moden (7/7/2016)


    Tava (7/6/2016)


    Since the export process can not change...

    It comes from a computer. It can be changed. It's ok to do a temporary work around but they need to fix it at the source.

    I hear you and I agree it's the right way to fix it at the source.

    I've told them and tried to get it done but when the files are coming from a multi million dollar company and your company doesn't want to tell them the company to fix the root cause, then I haven't got a leg to stand on besides getting told it has to get done or we can lose a contract.

    In the end I got no choice. I still need a job lol. There's no right or wrong way in this case its just get it done so it works and continues to always work.

  • Jeff Moden (7/7/2016)


    Tava (7/6/2016)


    Since the export process can not change...

    It comes from a computer. It can be changed. It's ok to do a temporary work around but they need to fix it at the source.

    Glad it worked. Generally you'll find that if you know there's going to be "bad" data, it's much easier in SSIS to capture it in some kind of transformation, rather than attempting a filter at the source. If you also then know, more often than not, why it's wrong and how to fix it, you can then further transform the "bad" data and actually put it into your final destination. What ETL is for 🙂

    Thom~

    Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
    Larnu.uk

  • Tava (7/7/2016)


    Jeff Moden (7/7/2016)


    Tava (7/6/2016)


    Since the export process can not change...

    It comes from a computer. It can be changed. It's ok to do a temporary work around but they need to fix it at the source.

    I hear you and I agree it's the right way to fix it at the source.

    I've told them and tried to get it done but when the files are coming from a multi million dollar company and your company doesn't want to tell them the company to fix the root cause, then I haven't got a leg to stand on besides getting told it has to get done or we can lose a contract.

    In the end I got no choice. I still need a job lol. There's no right or wrong way in this case its just get it done so it works and continues to always work.

    Agreed and sorry for the "preaching". I've had to put up with that many times myself.

    It is amazing, though, that such "multi million dollar" companies can't or won't get something so simple right. Forcing other companies to create work arounds is what I call "distributing the development load" and might be how they got to be "multi million dollar companies"... they never have to spend money on fixing their own stuff. 😛

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.


    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • Jeff Moden (7/7/2016)


    Tava (7/7/2016)


    Jeff Moden (7/7/2016)


    Tava (7/6/2016)


    Since the export process can not change...

    It comes from a computer. It can be changed. It's ok to do a temporary work around but they need to fix it at the source.

    I hear you and I agree it's the right way to fix it at the source.

    I've told them and tried to get it done but when the files are coming from a multi million dollar company and your company doesn't want to tell them the company to fix the root cause, then I haven't got a leg to stand on besides getting told it has to get done or we can lose a contract.

    In the end I got no choice. I still need a job lol. There's no right or wrong way in this case its just get it done so it works and continues to always work.

    Agreed and sorry for the "preaching". I've had to put up with that many times myself.

    It is amazing, though, that such "multi million dollar" companies can't or won't get something so simple right. Forcing other companies to create work arounds is what I call "distributing the development load" and might be how they got to be "multi million dollar companies"... they never have to spend money on fixing their own stuff. 😛

    ahaha preach away Jeff... we have a joke in the office which references a quote from Homer when he changes his name to max power. totally fits in here.

    Max Power: Kids: there's three ways to do things; the right way, the wrong way and the Max Power way!

    Bart: Isn't that the wrong way?

    Max Power: Yeah, but faster!

    :-P:-P:-P

  • Jeff Moden (7/7/2016)


    It is amazing, though, that such "multi million dollar" companies can't or won't get something so simple right. Forcing other companies to create work arounds is what I call "distributing the development load" and might be how they got to be "multi million dollar companies"... they never have to spend money on fixing their own stuff. 😛

    It took some long tens and tens of years before car manufacturers were made to recall and fix their faulty productions on their own expense.

    I wonder how long before IT producers will be placed on the same path.

    _____________
    Code for TallyGenerator

Viewing 10 posts - 1 through 9 (of 9 total)

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