locked
Error converting data type DBTYPE_DBTIMESTAMP to datetime when moving data from Oracle to Sql Server RRS feed

  • Question

  • Hi,

    I tried move data from Oracle 10g to Sql Server 2005 with the Sql Server Import & Export wizards.  But it was falling over with the error message "Error converting data type DBTYPE_DBTIMESTAMP to datetime" with details attached below:

    The Oracle table had a date column  and its value is "01/06/0907".  As far as I know Sql Server cannot handle year earlier than 1753 and I was guessing it was the cause of the error.


    Can anyone help ?




    -----------------------------------------------------------------------
    Details Error Messages

    Error 0xc0202009: Data Flow Task: SSIS Error Code DTS_E_OLEDBERROR. An OLE DB error has occurred. Error code: 0x80004005.
    An OLE DB record is available. Source: "Microsoft SQL Native Client" Hresult: 0x80004005 Description: "Error converting data type DBTYPE_DBTIMESTAMP to datetime.".
    (SQL Server Import and Export Wizard)

    Error 0xc0047038: Data Flow Task: SSIS Error Code DTS_E_PRIMEOUTPUTFAILED. The PrimeOutput method on component "Source - TRANSFER_FUNDS" (1) returned error code 0xC0202009. The component returned a failure code when the pipeline engine called PrimeOutput(). The meaning of the failure code is defined by the component, but the error is fatal and the pipeline stopped executing. There may be error messages posted before this with more information about the failure.
    (SQL Server Import and Export Wizard)

    Error 0xc0047021: Data Flow Task: SSIS Error Code DTS_E_THREADFAILED. Thread "SourceThread0" has exited with error code 0xC0047038. There may be error messages posted before this with more information on why the thread has exited.
    (SQL Server Import and Export Wizard)

    Error 0xc0047039: Data Flow Task: SSIS Error Code DTS_E_THREADCANCELLED. Thread "WorkThread0" received a shutdown signal and is terminating. The user requested a shutdown, or an error in another thread is causing the pipeline to shutdown. There may be error messages posted before this with more information on why the thread was cancelled.
    (SQL Server Import and Export Wizard)

    Error 0xc0047021: Data Flow Task: SSIS Error Code DTS_E_THREADFAILED. Thread "WorkThread0" has exited with error code 0xC0047039. There may be error messages posted before this with more information on why the thread has exited.
    (SQL Server Import and Export Wizard)

    Friday, April 24, 2009 5:14 AM

Answers

  • Have you tried explicitly converting the oracle datetime to a character value, as outlined in this thread?

    http://forums.sqlwire.com/showthread.php?t=39889
    Aaron Alton | thehobt.blogspot.com
    Friday, April 24, 2009 5:53 AM
  • There are two potential issues here

    One is that the Oracle timestamp datatype stores dates in a nanosecond precision, and SQL cannot store this precision. The fix that Aaron references will work for this (ie convert the date to a SQL-Server readable date in a view before it hits SQL Server). I believe that this issue is fixed in the Oracle 11g drivers, so it may be worth upgrading your Oracle client. I read this on a Microsoft BI blog post some time back, but haven't confirmed it personally.

    Of course, since you're on 2005 you will also have the issue of storing a pre-1793 year, so you'll also need to cater for this in your code.

    Regards

    Ewan
    Monday, April 27, 2009 11:59 AM

All replies

  • Have you tried explicitly converting the oracle datetime to a character value, as outlined in this thread?

    http://forums.sqlwire.com/showthread.php?t=39889
    Aaron Alton | thehobt.blogspot.com
    Friday, April 24, 2009 5:53 AM
  • There are two potential issues here

    One is that the Oracle timestamp datatype stores dates in a nanosecond precision, and SQL cannot store this precision. The fix that Aaron references will work for this (ie convert the date to a SQL-Server readable date in a view before it hits SQL Server). I believe that this issue is fixed in the Oracle 11g drivers, so it may be worth upgrading your Oracle client. I read this on a Microsoft BI blog post some time back, but haven't confirmed it personally.

    Of course, since you're on 2005 you will also have the issue of storing a pre-1793 year, so you'll also need to cater for this in your code.

    Regards

    Ewan
    Monday, April 27, 2009 11:59 AM
  • Hi,

    i had a similar problem with openquery and oracle dates i use this code snippet as workaround (replace any date before 1.1.1900 with NULL):

    SELECT
      *
    FROM
      OPENQUERY(LINKED_SERVER, '
        SELECT
            data1
          , data2
          , CASE WHEN datefield < TO_DATE(''19000101'' ''YYYYMMDD'') THEN NULL ELSE datefield END datefield
        FROM
          tableX
    ')
    Inside the OPENQUERY there are no " chars this are all two ' chars.

    With a Statement like this you can do a SELECT INTO TABLE to transfer all data from one oracle table to an ms-sql table.

    42
    • Proposed as answer by Lincoln_Felix Friday, May 8, 2009 8:56 AM
    Wednesday, April 29, 2009 10:32 PM
  • (English Version)
    Error: Error converting data type DBTYPE_DBTIMESTAMP to datetime

    Look this sample in MS Access

    Import information data converted


    SELECT     Date_Sample
    FROM        OPENROWSET('Microsoft.Jet.OLEDB.4.0', 'C:\Sample.mdb'; 'admin'; '',
                          'SELECT IIf([access_date_field] Is Not Null,Format([access_date_field],"dd/mm/yyyy HH:nn:ss"),-1) AS Date_Sample FROM table_sample')
                           AS ACC_TBL

    Thats it!
    Sorry my english

    (Portuguese-BR Version)
    Erro: Error converting data type DBTYPE_DBTIMESTAMP to datetime

    Olhem este exemplo em MS Access

    Importem a informação convertida

    SELECT     Date_Exemplo
    FROM        OPENROWSET('Microsoft.Jet.OLEDB.4.0', 'C:\Exemplo.mdb'; 'admin'; '',
                          'SELECT IIf([campo_data_access] Is Not Null,Format([campo_data_access],"dd/mm/yyyy HH:nn:ss"),-1) AS Data_Exemplo FROM tabela_exemplo')
                           AS ACC_TBL

    É isto!

    Lincoln
    Friday, May 8, 2009 9:07 AM