Oracle Driver Installation for SSIS 2008 R2

16/01/2011

9 תגובות

 

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.

Preparing for the Installation

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.

64 Bit

Windows Server 2008 R2 x64 must download the 64-bit Oracle 11g client from:

http://www.oracle.com/technology/software/products/database/oracle11g/111060_win64soft.html

You MUST accept the OTN agreement before you can download the client. Make sure you download only the client and NOT the Oracle database. Look for the heading ‘Oracle Database 11g Release 1 Client (11.1.0.6.0) for Microsoft Windows (x64)

   

32 BIT

Windows Server 2008 R2 x86 must download the 32-bit Oracle 11g client from:

http://www.oracle.com/technology/software/products/database/oracle11g/111060_win32soft.html

You MUST accept the OTN agreement before you can download the client. Make sure you download only the client and NOT the Oracle database. Look for the heading ‘Oracle Database 11g Release 1 Client (11.1.0.6.0) for Microsoft Windows (32-bit)

   

*

Download both clients for x64 systems and install x86 first then x64.

Oracle 32-bit Client Installation

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

clip_image008

2. Once file extraction is complete navigate to the \client directory and launch setup.exe. The Oracle client installer starts, click next to continue.

clip_image010

3. On the installation type, choose custom. Click Next to continue.

clip_image012

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

clip_image014

5. Click next to proceed past the product-specific prerequisite checks.

clip_image016

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)

clip_image018

7. Accept the default port assignment for Microsoft Transaction Server and click next to continue:

clip_image020

8. Click install on the summary screen to install the Oracle 32-bit client:

clip_image022

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.

Oracle 64-bit Client Installation

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

clip_image024

2. Once file extraction is complete navigate to the \client directory and launch setup.exe. The Oracle client installer starts, click next to continue.

clip_image025

3. On the installation type, choose custom. Click Next to continue.

clip_image026

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

clip_image028

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.

clip_image030

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)

clip_image031

7. Accept the default port assignment for Microsoft Transaction Server and click next to continue:

clip_image032

8. Click install on the summary screen to install the Oracle 64-bit client:

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.

Post Oracle Client Configuration Changes (Required)

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.

הוסף תגובה
facebook linkedin twitter email

כתיבת תגובה

האימייל לא יוצג באתר. שדות החובה מסומנים *

9 תגובות

  1. Charles Enwesi03/02/2012 ב 17:25

    Thanks for this write up… saved me from some nasty troubleshooting

    הגב
  2. tinnu16/03/2012 ב 01:49

    Thanks for this article…. It saved me…… God bless you……..

    הגב
  3. Azim06/06/2012 ב 13:31

    It worked for me too, I went bit farther and used Oracle 11 g r2 client for both 64 and 32 bit versions. Few changes were also needed in SQLNET.ora client files.

    Thanks a lot for sharing this great help.

    הגב
  4. Niahr15/03/2013 ב 12:12

    Thanks a lot for this explaination. It worked as a charm in 2008 R2, but not in SQL Server 2012.

    Can you please help me how to setup in SQl server 2012

    הגב
  5. fSiKPlwRRF04/06/2013 ב 05:41

    Thanks for sharing, this is a fantastic blog article. Will read on…

    הגב
  6. ZVBjGEzCDAchTVkhQH19/06/2013 ב 14:07

    sVpCzh A round of applause for your blog article.Thanks Again. Will read on…

    הגב
  7. RpaHRvzbnhuaapxamvq26/07/2013 ב 08:28

    TWr5SM Really appreciate you sharing this blog.Really looking forward to read more. Really Great.

    הגב
  8. uztATpDVvkCoVqYZJg16/10/2013 ב 12:21

    ufY4nH A big thank you for your article post.Thanks Again. Cool.

    הגב
  9. Pingback: Oracle In Oraclient11g Home 1 Download | Health Payday Loans