Default to NULL instead of blank/empty string during flat file import
Hi,
In SSIS flat file import using fastload, I'm trying to import data into SQL 2005 previously created tables.
The table may contain column that are NULLable BUT there is NO DEFAULT for them.
If the incoming data from flat files contains nothing either between the delimeters, how can I have a NULL value inserted in the column instead of blank/empty string?
I didn't find an easy flag unless I'm doing something wrong. I know of at least two ways to do it the hard way:
1- set the DEFAULT(NULL) for EVERY column that needs this behaviour
2-set up some Derived Column option in the package to return NULL if the value is missing.
Both of the above are time consuming since I'm dealing with many tables. Is there a quick option to default the value to NULL WHEN there is NO data ELSE insert the data itself? So the same behavior that I have right now except that I want NULL in place of empty string/blank in the varchar(x) columns.
Thanks
Anatole
[1012 byte] By [
avfarci] at [2007-12-23]
Thanks for the rpely, Jamie.
I understand the difference between NULL and empty string, but why is this functionality different from SQL 2000. If you import the same table in DTS (SQL 2000), you will get a value of NULL in the columns instead of empty string/blank.
Also if you perform an INSERT into the SQL 2005 table omitting the column that allows null, it will default to NULL instead of empty string.
It seems that there should have been an option similar to Keep NULLs on the fastload to allow default to NULL IF the column is NULLable instead of writing the Derived columns. I guess this falls into the same category where the SSIS now require us to TRIM the data in derived column while in previous versions, this was given.
Anatole
 avfarci wrote: |
| Thanks for the rpely, Jamie. I understand the difference between NULL and empty string, but why is this functionality different from SQL 2000. If you import the same table in DTS (SQL 2000), you will get a value of NULL in the columns instead of empty string/blank. |
|
Then I would argue that the behaviour in DTS2000 is erroneous. I guess it depends on your interpretation. Obviously the SSIS dev team agree because they have changed the behaviour.
Besides, there has never been an effort to make things behave the way they did in DTS. SSIS is a deliberate move away from DTS whilst keeping the (very few) good things that were in DTS (such as precedence constraints).
| avfarci wrote: |
| Also if you perform an INSERT into the SQL 2005 table omitting the column that allows null, it will default to NULL instead of empty string. |
|
Well yes, of course it does. If you don't insert anything into the column then there won't be anything in it i.e. NULL
| avfarci wrote: |
| It seems that there should have been an option similar to Keep NULLs on the fastload to allow default to NULL IF the column is NULLable instead of writing the Derived columns. |
|
Well feel free to request this behaviour be changed if you want it. I'm pretty sure it won't happen tho.
| avfarci wrote: |
| I guess this falls into the same category where the SSIS now require us to TRIM the data in derived column while in previous versions, this was given. |
|
Again, that is the inherent naffness of DTS that has been changed. Why should your ETL arbitrarily trim columns for you? "It shouldn't" is the answer. If you want the columns trimmed then it should be done explicitly - the ETL tool should not guess as to whether you want it done or not. This was one of the big problems in DTS - it would change your data without you asking it to. Sometimes the results were quite acceptable (as in your example) but its still a very dangerous practice. SSIS is a deliberate step away from that.
-Jamie
I actually found my answer by going thru the source and destinations again.
In the Flat File Source Editor dialog box, there is a check box that will create this behaviour.
- Retain null values from the source as null values in the data flow
Specify whether to keep null values when data is extracted. The default value of this property is false. When this value is false, the Flat File source replaces null values from the source data with appropriate default values for each column, such as empty strings for string columns and zero for numeric columns.
Thank you
Anatole
