Default Values not Applied

  • I have a SSIS Package the is inserting data into multiple table.

    Many of these tables contain boolean fields.

    However, in the source, these can be either True, False or NULL. In the destination they are only True or False, with a Default Value of False set on each column.

    Despite the fact that my OLE Data Destination has "Keep NULLs" unticked, and also the Fastload option of CHECK_CONSTRAINTS set it always errors as soon as a Null value appears in the data flow.

    Can anyone advise me on the best practice to avoid this?

    Thanks,

    Paul

  • You could add a derived column with an IsNull() check in it to force your source data to be compliant with your destination's requirements.

    If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.

  • Thanks for your reply Phil. Much appreciated... and a good idea.

    Can you help with the Expression?

    I have tried the following, which fails:

    isnull(likes_fish) ? 0 : [likes fish]

    and

    !isnull(likes fish) ? [likes fish] : 0

    and

    isnull(likes fish) : 0

    I also tried something like

    isnull(likes fish) ? 0 : ([likes fish] ==1 ? 1:0))

    Is this something you can help with?

    Paul

  • As phil suggests the best way would be to use IsNULL() but ... what's important is what to do u want if its a NULL value.. DO u what to change it to "FALSE".. you can write a simple IF in the derived Column

    ISNULL(ColumnName)? "statement": ColumnName

    Ex: ISNULL("TRUE")?"TRUE":"FALSE"

  • isnull([likes_fish]) ? FALSE : [likes_fish]

    should work.

    If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.

  • Thanks a bundle, Phil.

    That worked perfectly.

    Paul

Viewing 6 posts - 1 through 5 (of 5 total)

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