September 5, 2008 at 4:20 am
Help please.
I have a tilda delimited flat file that sometimes contains no value for Column X. Basically ~~ is what sometimes comes across. Column X has a Default that is supposed to set this as UNKNOWN. The Column, BTW, does allow nulls.
In SQL 2000, the DTS package loads this column correctly. When there's a value, the value gets populated. When there's not a value, the Default constraint takes over.
However, in SQL 2k5, the constraint doesn't work. It loads this as a blank value. Not even as a NULL.
Previous experience has taught me that in 2k5 if I try and change the column to NOT NULL, the Default constraint still won't work. In fact, the only time the Default will work in 2k5 is not to load the column at all in my insert statement.
So how do I get around this problem in SSIS? I need to load the column for those rows that have a value and I need the Constraint to automatically work for those rows that don't have a value. I have several columns like this and I don't want to have to manually code for each instance. After all, if I have to do that, what's the point of having a Default Constraint?
Any thoughts?
September 5, 2008 at 8:24 am
Figured it out. You do have to do a manual transformation of the columns in question. You can either set them to what you want the DEFAULT to be or you can set them to NULL (because SSIS reads them as blanks).
Jack Corbett posted this on another thread, but I'll re-iterate here. To change the values, use the following formula:
MyCol == "" ? NULL(DT_WSTR, < StringLength >) : MyCol
I did a replace instead of a new column and it seems to be working. The Defaults in the database work just fine.
Viewing 2 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply