Post reply

Derived Column and nulls...

  • September 2, 2008 at 12:22 pm

    #193105

    I am trying to take a "|" delimited flat file and drop it into a sql server table. One of the columns in the table will not allow a null but some of the rows in the flat file don't have data in that column so it errors when trying to load the data into the table. I am trying to redirect errors to a flat file but it doesn't seem to work in the case of nulls. It works for data type mismatches but not nulls. Aside from tweaking the table and allowing for nulls (may be an option, but probably not) is there something that can be done in the derived column object that would trap for that, that I might be overlooking? Not sure why the datatype mismatches redirect the error flatfile and not the nulls?

  • September 2, 2008 at 12:36 pm

    #866139

    A data type error happens in SSIS - because your data does not match the destination definition. The NULL is failing due to a database constraint - which happens on the database engine end and cannot be trapped on an individual record basis - especially if you are using any kind of bulk insert.

    The best solution for you in this instance would be to use a conditional split right before your destination and throw the NULL records into your text file from there.

  • September 2, 2008 at 12:37 pm

    #866140

    You can try to do a conditional split and check if the column is null before inserting into destination.

    There are some null functions in Conditional Split transformation which can help.

    try it out.

    Regards
    Venkat
    http://sqlblogging.blogspot.com[/url]

  • September 2, 2008 at 3:25 pm

    #866247

    The Conditional Split seem to work pretty good 🙂 . My only issue now is I can't seem to get it to write to the same textfile during the package execution. I was hoping to write the type mismatch rows to the same file as the null values that are now split out but it sure didn't like that too much. The first process (type mismatch in derived column) seems to keep a lock on the textfile while the second process (conditional split) is trying to connect to the same file. There aren't any flat file destinations that are available with more than one input are there? Will I have to use some sort of two file merge/concatenation process at the end of this particular data flow or will it have to be in a different and subsequent data flow?

  • September 2, 2008 at 4:36 pm

    #866275

    Use a union all transformation to merge the rows and point to one destination.

    Regards
    Venkat
    http://sqlblogging.blogspot.com[/url]

  • September 3, 2008 at 4:24 am

    #866404

    The UNION ALL is probably the best solution. You may also be able to simply set the "RetainSameConnection" property on the file connection manager to True.

  • September 3, 2008 at 4:46 pm

    #866871

    Good call! Thanks for the help!!! 😀

  • September 3, 2008 at 5:46 pm

    #866888

    I seen many, many people get burned by importing data directly to the final table. It would be much better to import it to a staging table and validate the data first.

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

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

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