Default Values not Applied

Question

Default Values not Applied

Paul_Harvey

Hall of Fame

Points: 3822
More actions
February 25, 2010 at 8:43 am

#145519

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

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

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

Phil Parkin SSC Guru Points: 246965 More actions · Answer 1

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.

The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin

Paul_Harvey Hall of Fame Points: 3822 More actions · Answer 2

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

divyanth SSCarpal Tunnel Points: 4303 More actions · Answer 3

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"

Phil Parkin SSC Guru Points: 246965 More actions · Answer 4

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

should work.

The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin

Paul_Harvey Hall of Fame Points: 3822 More actions · Answer 5

Paul_Harvey

Hall of Fame

Points: 3822

March 1, 2010 at 6:13 am

#1126423

Thanks a bundle, Phil.

That worked perfectly.

Paul