SQL Server Performance

Import from Oracle

Discussion in 'ALL SQL SERVER QUESTIONS' started by acki4711, Apr 17, 2012.

  1. acki4711 Member

    Hi all,
    Want to import data from oracle to sql server 2008 r2.
    Therefor I installed oracle instance client (11gr2 x64) on the server.
    Connecting with ODBC DSN and SQLPlus works.
    When I try now Import Wizzard and choose Oracle Provider for OLE DB I get:
    The 'OraOLEDB.Oracle' provider is not registered on the local machine. (System.Data)

    and with Oracle Data Provider for .net I get:
    TITLE: SQL Server Import and Export Wizard
    ------------------------------
    An error occurred which the SQL Server Integration Services Wizard was not prepared to handle.
    ------------------------------
    ADDITIONAL INFORMATION:
    Exception has been thrown by the target of an invocation. (mscorlib)
    ------------------------------
    Exception has been thrown by the target of an invocation. (mscorlib)
    ------------------------------
    The type initializer for 'Oracle.DataAccess.Client.OracleClientFactory' threw an exception.
    ------------------------------
    The provider is with the version of the Oracle client not compatible (Oracle Data Provider for .NET)

    What should I install to make this working ???
    TIA
    acki4711
  2. acki4711 Member

    Removed instant client and installed x86 client (user defined, odbc, oledb + odbc 4 .net) added TNS_ADMIN variable and all works like a charme...
    Why does sql server import wizard needs the x86 client from oracle and cannot handle oracle x64 driver ???
  3. Madhivanan Moderator

    It is because a 32 bit SQL Server can only recognise a oracle client of 32 bit only
  4. acki4711 Member

    Hmm, no, my installation os and sql server 2008 r2 is x64 ????
  5. Madhivanan Moderator

    Are you sure? Run the following and see the version

    select @@version
  6. acki4711 Member

    Yes I am sure

Share This Page