SSIS 64-bit environment access oracle11g

SSIS needs to install 64-bit Oracle Provider in order to have SSIS access to Oracle on a 64-bit machine, but the biggest problem is that SSIS is executed in two components, DTExec.exe (32-bit version) and DTExec.exe (64-bit version), which exist under Program Files (x86) and the Program Files directory. There is no problem when SSIS is executing or exporting data (64-bit version) with import

The biggest problem, however, is that SSIS uses only 32-bit versions of the bids during the design phase, so if you want to test the connection at development time, the corresponding 32-bit provider will not be found, and the problem with the initialization provider has occurred (so people will question that I have already installed, why he said not to find)

For this reason, the 32-bit and 64-bit Oracle Client must be installed at the same time.
The steps for installing the Oracle Client & OLE DB components in a 64-bit Windows environment are described below (my operating system is Windows Server R2):
1, the need to prepare 32-bit and 64-bit versions of the Oracle Client, we recommend the use of version 11.1.0.7.0, because if the use of 10.2.0.1 version requires the installation of two Oracle patches (#4547817 & #5383042), If the above patch is not installed, you will encounter Ora-12154:tns: The specified connection ID could not be resolved.
2. If you have an older Oracle clients on your system, copy the backup Tnsnames.ora and Sqlnet.ora files. (located in the%oracle_home%/network/admin/directory)
3. Install and delete the old Oracle client and the installation directory (you need to restart the computer to delete the directory).
4. To perform Oracle 11g client setup.exe on a 64-bit SQL Server machine, you must first install the 32-bit version.
5. Select [Admin] mode to install all (professional-level people can choose their own mode).

6. Select the appropriate language next, then modify the path as needed

Here I change the 32-bit path folder to Client_1,
Change the 64-bit path folder to Client_2.
7. Replace the Tnsnames.ora with the%oracle_home%/network/admin folder of the newly installed client after installation, and replace both 32-bit and 64-bit. Otherwise, a TNS error is prompted when creating an Oracle data source using Plsql or bids.
P.S. If you use your own NET Manager Configuration service after you have installed 2 clients (32-bit and 64-bit), the configuration is 64-bit, that is, the configured Ora has a 64-bit client directory in the Admin folder. It is not always possible to use bids to connect to Oracle (unless you use the Ip\ service name method)
8. Repeat the above steps and install the 64-bit client again

P.S. The installation will appear [Oraclemtsrecoveryservice already exist] or some path value too many error messages, please click [Ignore] continue to install, of course, can also find a solution to solve the better, but these problems are not very serious.

9, modify the following registry settings, and then restart the computer
HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\MSDTC\MTxOCI
Hkey_local_machine\software\wow6432node\microsoft\msdtc\mtxoci
Change the value to
OracleOciLib = Oci.dll
OracleSqlLib = Orasql11.dll (old value is: SQLLib80. dll)
OracleXaLib = Oraclient11.dll (old value is: Xa80.dll)

But remember, when you use bids, it's based on a 32-bit data provider, As a result, the data is read in both development and debugging, using 32-bit Oracle OLE DB. However, when the SSIS wrapper is executed to 64-bit SQL Server, he will use the 64-bit Oracle OLE DB