Tag Archives: Date Conversion

Oracle 1/1/1700 date causes error in SSIS load to SQL Server 2008

1 Reply

When loading DATE type data from an Oracle database into a datetime column in a SQL Server database using SSIS an error such as this can occur:

 

[OLE DB Destination [638]] Error: There was an error with input column “EFFECT_DTE” (998) on input “OLE DB Destination Input” (651). The column status returned was: “Conversion failed because the data value overflowed the specified type.”

 

This can be caused when the data value in the Oracle database is 1/1/1700, Oracle’s implementation of a null date. To fix this, create a Derived Column transformation after the OLE DB Source item. In the Derived Column, using EFFECT_DTE as the example field name, enter the following:

Derived Column Name

Derived Column

Expression

EFFECT_DTE

Replace ‘EFFECT_DTE’

EFFECT_DTE == (DT_DATE) “1700-01-01” ? NULL(DT_DATE) : EFFECT_DTE

 

This expression checks if the field is 1/1/1700 and if so converts it to a null date for SQL Server otherwise it passes the date through.

Advertisement

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

2 Replies

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.