In an SSIS package I am using a flat file source with a date column, and some of the dates are empty strings. I want these empty strings to be treated as null when the data from the file is loaded into the database. I have checked the "Retain null values from the source as null values in the data flow" checkbox in the Flat File Source Editor, but when I execute the task I get the error "The value could not be converted because of a potential loss of data". How can I get the SSIS package to treat the empty date strings as nulls?
+1
A:
Use the following expression:
TRIM( [ColumnName] ) == “” ? (DT_STR, 8, 1252)NULL(DT_STR, [length], 1252) : [ColumnName]
klabranche
2009-08-19 01:24:10
Derived column transformations seem to work, I was just hoping that the "Retain null values" checkbox would handle this automatically.
Scott
2009-08-19 01:32:19
In this case it's coming in as an empty string so it thinks it's converting it and is erroring since it believes a truncation may result.
klabranche
2009-08-19 01:53:20