Data Flow Task - Have Null values take table default
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
[595 byte] By [
JPasqua] at [2007-12-21]
You can try to use the Derived Column transform and replace your null values with the expression like this:
ISNULL([<your_column_with_nulls>]) ? : <default_value> ? ([<your_column_with_nulls>]
Make sure to replace the value of your column (named here as "your_column_with_nulls") with the derived value by choosing >>Replace "your_column_with_nulls"<< from the appropriate drop-down in the UI.
HTH.
Thanks Bob- Yeah, I was trying to avoid this route, because doing so will require me to add these "Default" values into my SSIS package, when they're already specified in my SQL table (and have to keep track/update them in 2 places).
I'd rather find a way to just use the SQL table's default values if a NULL comes across in the data flow. I have to believe there's a way to do this... Anyone?
I believe it should work if you pass nulls to the destination adapter. However, if you are using flat files, have you been sure to check the "Retain Nulls" box in the source adapter?
Donald
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 working, let me know!
