JHFB February 2016

importing a flat file that contains both nulls and blanks

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?

Answers


Bruce Dunwiddie February 2016

Sucks, but what I would suggest is to use " " for empty string, with the setting to retain null values. Then, in a derived column or a script component transformation, if the value(s) are not null perform a trim on them, or explicitly check for a value of a single space and set it to an empty string.

Post Status

Asked in February 2016
Viewed 2,763 times
Voted 8
Answered 1 times

Search




Leave an answer