SSIS Package - Oracle client and networking components were not found.

  • HookSQLDBA7

    SSChampion

    Points: 10552

    We are running SQL Server 2012 EE 64-Bit Eval Copy on Windows Server 2008 R2 EE 64-Bit SP1 on our DB Server. We have installed Oracle 11G Client Install. We did a Custom Install and selected the following:

    SQL*Plus

    Oracle Net

    Oracle ODBC Driver

    Oracle Services For Microsoft Transaction Server

    Oracle Objects for OLE

    Oracle Provider for OLE DB

    Oracle Data Provider for .NET

    Configured sqlnet.ora and tnsnames.ora files.

    Tested tnsping to our Oracle Server/Database and it worked.

    Tested sqlplus to our Oracle Server/Database and it worked.

    Tested ODBC (System DSN and 'Oracle in OraClient11g_home1' driver) to our Oracle Server/Database and it worked.

    Set up Linked SQL Server to Oracle using the following and it worked:

    --With Microsoft OLE DB Provider for Oracle, use the Oracle server alias

    --(that is configured in the TNSNames.Ora file) for the @datasrc parameter.

    ------------------------------------------------------------------------

    -- Test_DBTest Link

    ------------------------------------------------------------------------

    -- Add a linked server for Test DBTest.

    USE [master]

    GO

    EXEC sp_addlinkedserver

    @server = 'Test_DBTest_shared'

    ,@srvproduct = 'DBTest'

    ,@provider = 'OraOLEDB.Oracle'

    ,@datasrc = 'DBTest'

    GO

    -- Add a login for the the remote linked server.

    USE [master]

    GO

    EXEC sp_addlinkedsrvlogin

    @rmtsrvname = 'Test_DBTest_shared'

    ,@useself = 'False'

    ,@locallogin = NULL

    ,@rmtuser = 'shared'

    ,@rmtpassword = 'Test1232'

    Then to create the SSIS Package, I go into Microsoft Visual Studio 2012 (it opens and displays Microsoft Visual Studio 2010 Shell). When I try to create a new OLE DB Connection to Oracle via Connection Manager, I select the "Native OLE DB\Microsoft OLE DB Provider to Oracle" I get the following error:

    "Test connection failed because of an error in initializing provider. Oracle client and networking components were not found. These components are supplied by Oracle Corporation and are part of the Oracle Version 7.3.3 or later client software installation. Provider is unable to function until these components are installed."

    The Connection Manager does not display any other Oracle Providers. I saw the following during my research:

    "Problems

    Because BIDS is a 32 bit application, as well on 32 as on 64 bit machines, it cannot see the 64 bit driver for Oracle. Because of this, connecting to Oracle from BIDS on a 64 bit machine will never work when you install the 64 bit Oracle client."

    Is BIDS and Visual Studio the same application or uses the same code? Since, I installed SQL Server 2012 EE 64-Bit Eval Copy and the OS is 64-Bit, does this mean a 64-Bit version of Visual Studio was installed? What may I try to get this resolved?

    Thanks in advance, Kevin

  • prvmine

    SSCertifiable

    Points: 6968

    this link should help - it was written for SSIS 2008 R2 - and is applicable to SSIS 2012

    http://sqlblog.com/blogs/jorg_klein/archive/2011/06/09/ssis-connect-to-oracle-on-a-64-bit-machine.aspx

    this link also address SSIS 2012 connecting to Oracle 64 bit via ODAC

    http://social.msdn.microsoft.com/Forums/sqlserver/en-US/f2e365f0-45cc-43f0-be70-9358701387a2/connecting-to-oracle-11g-on-64bit-x64-machine-ssis-2012

  • HookSQLDBA7

    SSChampion

    Points: 10552

    Thanks prvmine. Installing the Oracle 32-Bit Client resolved the issue. Just wondering why Microsoft has not come up with 64-Bit Development Tools (BIDS, VSS, etc.)?

    Thanks, Kevin

  • prvmine

    SSCertifiable

    Points: 6968

    Glad you resolved the issue Kevin.

    No ideal about MS moving to 64-bit development tools, but wish they would.

    - prv

  • Koen Verbeeck

    SSC Guru

    Points: 258965

    kevinsql7 (7/2/2013)


    Thanks prvmine. Installing the Oracle 32-Bit Client resolved the issue. Just wondering why Microsoft has not come up with 64-Bit Development Tools (BIDS, VSS, etc.)?

    Thanks, Kevin

    I don't think VS will move to 64-bit soon.

    So you better always install the 32-bit and 64-bit clients on your machine.

    Need an answer? No, you need a question
    My blog at https://sqlkover.com.
    MCSE Business Intelligence - Microsoft Data Platform MVP

Viewing 5 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply