If your SSIS server will require access to Oracle DB you can find interest in this blog. You must still request access and provide valid credentials to access the information on the Oracle warehouse. This section only instructs how to install and configure the Oracle driver for connectivity.
Download the Oracle client to your server’s local C:\ drive, use the table below to determine which installations or additional downloads you must perform. For x64 systems you MUST download BOTH clients and install BOTH clients starting with x86 first then x64.
You must install the Oracle 32-bit client first on an x64 Windows SSIS server, then install the Oracle 64-bit client on the Windows SSIS server!
1. Extract the contents of the .zip file into a directory
2. Once file extraction is complete navigate to the \client directory and launch setup.exe. The Oracle client installer starts, click next to continue.
3. On the installation type, choose custom. Click Next to continue.
4. Modify the installation location to the following values and click next to continue:
Oracle Base: Change to c:\oracle
Name: Leave as OraClient11g_home1
Path: Change to c:\oracle\product\11.1.0\client_32
5. Click next to proceed past the product-specific prerequisite checks.
6. On the available product components make the following selections then click next to continue:
Oracle Windows Interfaces 11.1.0.6.0 (accept every sub-selection that the installer automatically chooses)
Oracle Net 11.1.0.6.0 (accept every selection that the installer automatically chooses)
7. Accept the default port assignment for Microsoft Transaction Server and click next to continue:
8. Click install on the summary screen to install the Oracle 32-bit client:
9. The installation for Oracle 32-bit client is complete. If you have performed the 32-bit installation as part of the 64-bit installation procedures continue to the 64-bit installation process. If you are only installing the 32-bit Oracle client on a Windows Server 2008 R2 32-bit server skip the 64-bit installation process and perform the steps in the Post Oracle Client Configuration Changes to fully complete the setup.
You must install the Oracle 32-bit client first on an x64 Windows SSIS server, then install the Oracle 64-bit client on the Windows SSIS server!
1. Extract the contents of the .zip file into a directory
2. Once file extraction is complete navigate to the \client directory and launch setup.exe. The Oracle client installer starts, click next to continue.
3. On the installation type, choose custom. Click Next to continue.
4. Modify the installation location to the following values and click next to continue:
Oracle Base: Change to c:\oracle (the same as 32-bit, will not overwrite, just creates a second home)
Name: Leave as OraClient11g_home2
Path: Change to c:\oracle\product\11.1.0\client_64
| 64 BIT | Unique to a x64 installation; Use the same base Oracle installation which is c:\oracle, however create a second Oracle home and name this one OraClient11g_home2. The effectively gives the server two client homes one 32-bit and the other 64-bit. Depending on the SSIS package developed in BIDS the server will correctly call the OLE objects for the underlying driver architecture, either 32 or 64 bit. |
| | |
| * | CAREFULLY examine the information in the Name and Path text boxes, subtle changes. |
5. Click next to proceed past the product-specific prerequisite checks.
6. On the available product components make the following selections then click next to continue:
Oracle Windows Interfaces 11.1.0.6.0 (accept every sub-selection that the installer automatically chooses)
Oracle Net 11.1.0.6.0 (accept every selection that the installer automatically chooses)
7. Accept the default port assignment for Microsoft Transaction Server and click next to continue:
8. Click install on the summary screen to install the Oracle 64-bit client:
![clip_image022[1]](http://blogs.microsoft.co.il/blogs/bilive/clip_image0221_thumb_081F9817.jpg "clip_image022[1]")
| * | During installation with the 64-bit Oracle client you will receive an error stating that the OracleMTSRecoveryService already exists. When this message box pops-up, click Ignore to proceed. |
9. The installation for Oracle 64-bit client is complete, however you must continue to Post Oracle Client Configuration Changes to fully complete the setup.
To complete either the 32-bit or 64-bit Oracle client installation processes make the following registry changes:
1. For both 32-bit and 64-bit installations, open up REGEDIT and make the following registry changes:
HKEY_LOCAL_MACHINE\Software\Microsoft\MSDTC\MTxOCI
OracleOciLib contains the value oci.dll
OracleSqlLib contains the value orasql11.dll (old value is SQLLib80.dll)
OracleXaLib contains the value oraclient11.dll (old value is xa80.dll)
2. For 64-bit installations only:
HKEY_LOCAL_MACHINE\Software\Wow6432Node\Microsoft\MSDTC\MTxOCI
OracleOciLib contains the value oci.dll
OracleSqlLib contains the value orasql11.dll (old value is SQLLib80.dll)
OracleXaLib contains the value oraclient11.dll (old value is xa80.dll)
3. You must reboot your server for the changes to apply. Reboot your server.