64-bit environment on SSIS connection Oracle

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):

2. The 32-bit and 64-bit versions of the Oracle Client need to be prepared using version 11.1.0.7.0 because if you use the 10.2.0.1 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.
4. 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)
6. Jian and deleted old Oracle client and installation (requires a reboot to remove the catalog).
7. 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