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.
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.
About
A Market Street Solutions business intelligence advisory consultant with deep experience in Microsoft and Cognos data warehousing architecture, data modeling, data loading and reporting. Microsoft certified – MCITP, MCT (Microsoft Certified Trainer) - in Business Intelligence and Database Development, Wharton MBA, chess player, dog lover, and husband. I live in Chattanooga, Tennessee, with my wife Jill, son Henry, dogs Bubba and Caesar, and cats Priscilla, Elvis, Prudence and Precious.
Email me, please: will.thrash@marketstreetsolutions.com
Twitter: @willthrash1
Website: Market Street Solutions
-
Recent Comments
-
Archives
- April 2012 (1)
- September 2011 (1)
- April 2011 (1)
- June 2010 (1)
- May 2010 (1)
- April 2010 (1)
- March 2010 (1)
- January 2010 (1)
- December 2009 (1)
- May 2009 (3)
- April 2009 (2)
- March 2009 (5)
-
Categories
-
RSS
Entries RSS
Comments RSS
