none
Import from Oracle Rdb into SQL 2005

    Pertanyaan

  •  

    Hi All,

     

    I'm trying to import from Oracle Rdb database into SQL server 2005 via ODBC without much success.

     

    I've installed the correct Oracle Rdb driver and verified it all works ok by importing in to Access.

     

    Using the import wizard within SQL mgt studio and the following connection strings I get a password error

     

    Dsn=XRS;Driver={Oracle RDB Driver};sid=system

     

    TITLE: SQL Server Import and Export Wizard
    ------------------------------

    The operation could not be completed.

    ------------------------------
    ADDITIONAL INFORMATION:

    ERROR [HY000] [Oracle][ODBC][Rdb]%SQLSRV-F-GETACCINF, Oracle SQL/Services authorization failed
    ERROR [HY000] [Oracle][ODBC][Rdb]%SQLSRV-F-GETACCINF, Oracle SQL/Services authorization failed

    ------------------------------
    BUTTONS:

    OK
    ------------------------------

     

    I know the username/password combination to be correct.

     

    Is SSIS the way to go.  I've had a quick look at this and setup the ODBC connection but the column

    mappings box reports the following...

     

    error at data flow task [datareader source[1]; cannot aquire a managed connection

    from the run-time connection manager.

     

    Anyone point me towards any examples of reading in data via ODBC and storing in a SQL server database.

     

    Should point out that the source is not Oracle RDBMS (7,8 or 9) but Oracle Rdb which is a

    completely seperate product.  

     

    Thanks in advance

     

    Dave

     

     

     

     

      

     

     

     

    07 Desember 2007 12:16

Jawaban

  • If you can connect via ODBC via other methods (MS Access, etc...) then you can connect via SSIS.  I'd get that working first before trying to use SSIS.
    07 Desember 2007 13:55
    Moderator

Semua Balasan

  •  

    Hi,

     

    Thanks for that.  I've contacted the Oracle Rdb provider, a company called Attunity, who can supply

    a data provider for about £8,000. 

     

    Cheers

    Dave

    07 Desember 2007 13:52
  • If you can connect via ODBC via other methods (MS Access, etc...) then you can connect via SSIS.  I'd get that working first before trying to use SSIS.
    07 Desember 2007 13:55
    Moderator


  • Hello Dave,

    I am facing the same problem in the same environment and getting the same error message.
    Did you find a solution to the issue (hopefully without purchasing the data provider from Attunity)?

    Anybody tried the same in SQL 2008?
    Import wizard does not even offer the option to enter a connection string.

    Thanks in advance.

    joomo


    10 Oktober 2008 15:16
  •  

    Hi,

    Sorry no, I hit a blank wall on this and had to give up.  We have since implemented a system usign the logminer

    extract and SQL to update,

     

    Dave

    10 Oktober 2008 15:31
  •  

    Hi,

    If you do solve this problem would you pls post it here.  I would like to know

    how to achieve Rdb -> Sql Server connectivity.

     

    Regards & good luck.

     

    Dave

    13 Oktober 2008 9:04
  • Hi Dave,

     

    Thanks for your prompt response.

     

    I'll continue searching for a solution but there seem to be very few sources for SQL server 2008 which is what I am interested in primarily.

     

    Can you let me have some more details as to the solution with logminer you implemented?

     

    Thanks and have a nice day.

     

    13 Oktober 2008 10:34
  • Hi,

     

    We RMU/UNLOAD the Rdb journal files in out overnight processing, this basically converts them to a CSV file.

     

    Code Snippet
    $   RMU/UNLOAD/AFTER/STAT=30 dbroot G_AIJ_FILE: -
            /OPTIONS=FILE=GEN_METADATA:XPS_AIJUNL_01.OPT -
            /FORMAT=(DELIMIT,NULL="NULL",SEPARATOR="<|>")

     

     

     

    Records in this file are flagged either as a (M)odification or (D)elete so we read through it sequentially applying the changes to a holding database, once complet the changes are replicated over to the production SQL2005 using snapshot replication.

     

    Hope this helps.

     

    Dave

     

     

     

    14 Oktober 2008 9:20
  • I have the same problem with sql server 2008 , what i can do ?? 

    20 Juni 2011 15:37