I was creating an SSIS package to load data from a flat text file into a SQL Server 2008 database table and kept getting a data conversion error on a date field. It turned out that the date was empty in the flat file and that’s ok, it should just update the table with a value of NULL. The OLE DB Destination task was not having it. For some reason it did not recognize the empty field from a text file as a null value.

The specific error was:

There was an error with input column “datefieldname” on input “OLD DB Destination Input”. The column status returned was: “The value could not be converted because of a potential loss of data.”.

The solution was to change the data type in the Flat File Connection Manager to string for dates and add a Derived Column task. The formula to convert the string to a date was:

LEN(datefieldname)==0?NULL(DT_DBDATE):(DT_DBDATE) datefieldname

This checks for an empty string and returns a DB Date NULL value else it converts the string to a date.