0

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?

João Silva
  • 89,303
  • 29
  • 152
  • 158

1 Answers1

1

Use the following expression:

TRIM( [ColumnName] ) == “” ? (DT_STR, 8, 1252)NULL(DT_STR, [length], 1252) : [ColumnName]

Kevin LaBranche
  • 20,908
  • 5
  • 52
  • 76
  • 1
    Derived column transformations seem to work, I was just hoping that the "Retain null values" checkbox would handle this automatically. –  Aug 19 '09 at 01:32
  • 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. – Kevin LaBranche Aug 19 '09 at 01:53