64-bit environment on SSIS connection Oracle

Source: Internet
Author: User
Tags ole ssis

SSIS has the ability to allow SSIS to access Oracle on a 64-bit machine, which requires an 64-bit Oracle Provider. But the biggest problem is that the BI Development studio that SSIS uses at the design stage has only 32-bit versions, Therefore, if you want to test the data connection, you will not find the corresponding 32-bit metadata Provider.

So if you want to solve the way, you have to install the 32-bit and 64-bit Oracle Client at the same time.

The following is a step for installing Oracle Client & OLE DB components in a 64-bit Windows environment (my operating system is Windows Server R2):

  1. The 32-bit and 64-bit versions of the Oracle Client need to be prepared using version because if you use the version you need to install two Oracle patches (# 4547817 & # 5383042), if no patch encounters Ora-12154:tns: The problem of the specified connection ID cannot be resolved.
  2. If the system has an old Oracle clients, please copy the Tnsnames.ora and Sqlnet.ora files. (located in%oracle_home%/network/admin/'s catalogue)
  3. Jian and deleted old Oracle client and installation (requires a reboot to remove the catalog).
  4. Perform Oracle 11g Client Setup.exe on a 64-bit SQL Server machine,Must be installed in the first version of the meta-
    1. Choose "Customize" When choosing an installation type.

        1. Need to modify the path

      Here I changed the 32-bit path folder to client_32 and changed the 64-bit path folder to client_64.

        1. "Oracle NET 11.1", "sql*plus 11.1" need to be checked in the "Windows Interfaces 11.x.x" component as well as the video requirements.
        1. Replace the Tnsnames.ora with the new%oracle_home%/network/admin folder after installation
        2. Repeat the above steps to install the 64-bit client again

        1. Please click "Ignore" when installing the ""oraclemtsrecoveryservice "bug message.

        1. Modify the following registry settings, and then restart the machine HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\MSDTC\MTxOCI Hkey_local_machine\software\wow6432node \microsoft\msdtc\mtxoci
        2. Change the value to OracleOciLib = Oci.dll

      OracleSqlLib = Orasql11.dll (old value is: SQLLib80.dll)

      OracleXaLib = Oraclient11.dll (old value is: Xa80.dll)

      This is the time to access Oracle information normally in the 64-bit environment of the bids.

Remember, however, that when using bids, it is based on a 32-bit material provider, so it uses 32-bit Oracle OLE DB to access the information at the time of the launch and the bug. But when SSIS marshals to 64-bit SQL server, he will use the 64-bit Oracle OLE DB.

64-bit environment on SSIS connection Oracle

Related Article

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.