Error loading null dates from a text file into a database using SSIS.


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.

Advertisements

2 thoughts on “Error loading null dates from a text file into a database using SSIS.

  1. Deon

    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.

    Like

    Reply

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s