I am using SSIS to import flat files into our database. I have some control over the formatting of the files. The source contains both nulls and blanks - and they need to be preserved.
I currently have the file pipe delimited with double-quotes to qualify text. So this:
|"value a"|""||"value d"|
has "value a" in the first column, a blank in the second, null in the third, "valud d" in the fourth.
When I check the box Retain null values from the source as null values in the data flow, blanks turn into NULL. If I uncheck it, nulls turn to blanks.
How can I fix this in either SSIS or my modifying the spec of the flat file?