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:
This checks for an empty string and returns a DB Date NULL value else it converts the string to a date.
Hey Man. I know this post is old but if anyone else is having this problem just make sure you open the “Advanced Editor” on the Flat File source task and set the option “RetainNulls” to true on the Component Properties tab. This should resolve the issue without having to use a derived column.
Thanks Deon. You saved me some time.