Error converting data type DBTYPE_DBTIMESTAMP to datetime

Hi

I am trying to transfer data from Access 2000 database to SQL server 2005 via SSIS. My access database table has a field with data type Date/Time and SQL server table has a field with data type datetime. When I am running the SSIS package, it is throwing the following error and stops execution.

[SQL Server Destination [12466]] Error: An OLE DB error has occurred. Error code: 0x80040E07. An OLE DB record is available. Source: "Microsoft OLE DB Provider for SQL Server" Hresult: 0x80040E07 Description: "Error converting data type DBTYPE_DBTIMESTAMP to datetime.".

Please help.

Thanks in advance,
Ramzee

According to BOL (http://msdn2.microsoft.com/en-us/library/ms175970.aspx) the types can be converted, so that should work. Have you identified the values that fail? Using the error output is a very easy way to capture this data. My best guess for now is the values are out of range for the SQL datetime type. A derived column transformation could be used to massage these values depending on requirements.|||

Thanks Darren!

I used the script component to identify the wrong date entries. Now it works!

Thanks once again.

|||

I am having the same error. A simple select from Query Analyzer

Select * from HCPROD..HC.UNIT U

Even if I specifically reference a number column I get the same error

Select BLOCK_ID from HCPROD..HC.UNIT U

The way I see it is that there is a column, probably the DATE_MODIFIED column, somewhere in the database that has a date & time value that the driver cannot process. Why the error occurs even if I do not reference the column is a mystery.

I am using Microsoft OLE DB Provider for Oracle

Note that I have insured that every date field in the table is in 20th or 21st century

|||Some more information. We are using Oracle version 10g. I wonder if there is an updated driver from Microsoft? We searched and could not find one ourselves.|||SELECT TOP 10 * FROM [ORACLE_LOOKUP]..[SchemaName].[TableName] AlaisName

Error converting data type DBTYPE_DBTIMESTAMP to datetime

SELECT * FROM
OPENQUERY (
ORACLE_LOOKUP,
'SELECT TO_CHAR(DateColumnName) FROM TableName')

The following workaround was foud on the internet

A workaround would be to use the OPENQUERY function in SQL Server. This
function allows you to execute a pass-through query on the linked server.
By issuing a pass-through query, you can then take advantage of Oracle's
built-in functions to convert the date to a character data type or NULL.
Examples of both are as follows:

In the following example, the column "hiredate" is converted to a string in
the format of MM-DD-YYYY.

SELECT *
FROM OPENQUERY(, 'SELECT empno,
TO_CHAR(hiredate,''MM-DD-YYYY'') FROM scott.emp')

In this example we use a combination of two Oracle built-in functions,
DECODE and GREATEST to convert any hiredate that is earlier than 01/01/1753
(SQL Server's lower bound) to NULL.

SELECT *
FROM OPENQUERY(,'SELECT DECODE (hiredate,
GREATEST(hiredate, TO_DATE(''01/01/1753'',''MM/DD/YYYY'')), hiredate, NULL)
FROM scott.emp')

In case you are not familiar with Oracle built-ins, here is a description of
the two used in the query above.

DECODE is similar to a case statement in SQL Server. Its syntax is as
follows :

DECODE(, , , , ,...,
)

The is compared to each value. If there is a match,
the corresponding is returned. Otherwise, the value is
returned.

GREATEST returns the greatest value in the list of expressions.

GREATEST(, , ...)
Reply With Quote