June 6, 2006 at 11:05 am
Hi,
I have a data transform from a flat-file to a SQL server database.
Some of the flat-file fields have NULL values. The SQL table I'm
importing into does not allow NULL values in any field, but each field
has a Default value specified.
I need to have it so that if a null value comes across in a field using
the data transform, it takes the table default on import. I could of
sworn I had this working a few days ago, but I get errors now that
state I'm violating table constraints. Has anyone done this before?
Thanks
Jeff
June 7, 2006 at 6:49 am
This should just "work". It sounds as though the defaults aren't getting fired which is probably a problem in SQL. Mind you, you can supply table hints on the OLE DB Destination so maybe you are specifying something that you shouldn't be, or something you should be specifying that you are not.
I don't have a SSIS instance to hand so can't try this out.
-Jamie
Jamie Thomson
http://sqlblog.com/blogs/jamie_thomson
June 7, 2006 at 11:41 am
Ok so I've done some more poking, and this is what I've been able to come up with:
1) If a sql table column has a default value, but allows Nulls, I have gotten SSIS to successfully use the table's default value when it sends a Null field over to the SQL table. This works when FastLoadKeepNulls = False in the OLE DB Destination properties.
2) HOWEVER, even if a column has a default value, if Nulls are not allowed in the SQL table, SSIS bombs when it tries to send a null (or what should be the default) value over to the table. I've tried every combination of properties in the OLE DB Destination properties and can't get it to work when Nulls are disallowed in the SQL table.
Being able to at least do #1 will likely let me squeeze by, but I still think there's gotta be a way to get the default value input when nulls aren't allowed. If anyone can get #2 to work let me know!
August 13, 2011 at 2:15 pm
i have the same problem.
If you use the sql server destination type, the problem is not there.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply