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


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.

Advertisements

One thought on “Oracle 1/1/1700 date causes error in SSIS load to SQL Server 2008

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