ERROR: Failed to load OCI DLL

[Adeeb Mass'ad]

Error Message: Failed to load OCI DLL

SUMMARY

The error indicates a failure when loading the Oracle oci.dll. The error could occur when having multiple Oracle homes or permission problems.

SCENARIO 1

The user is working on Microsoft Windows 64Bit operating system and have installed the following components:

• Oracle Client 32Bit (Required for Design)
• Oracle Client 64Bit
• Microsoft SSIS Oracle Connector 64Bit

This is the required list of components for Developing on 64Bit Windows machine.

On some machines the following error was reported when running the Oracle component in 32Bit (Design time):
<b>

Error at Package [Connection manager "Oracle Connector 1"]: Failed to load OCI DLL.

</b>SOLUTION

First check that the environments are working and configured properly, and that SQLPlus is responding on both installations of the Oracle Client.

On the machine which we encountered the error, it was noticed that the Oracle Home for the 64Bit Oracle Client was defined in the 32Bit registry portion (Wow6432Node), this was causing the problem of loading the wrong oci.dll.

To workaround the problem, the user should define a dummy registry entry (Z_SSIS) as follows:

1. Open the regedit utility.
2. Locate the following Key:

HKEY_LOCAL_MACHINE\SOFTWARE\Wow6432Node\ORACLE.

ssis_oracle1.png

3. Right-Click on ORACLE node and click on New -> Key.
4. Call the new key Z_SSIS (to make sure it's the last entry).
5. Right-Click on the Z_SSIS node and click on New -> String.
6. Name the property ORACLE_HOME.
7. Double-Click on the ORACLE_HOME and set it to the location of the Oracle 32Bit installation home directory.

SCENARIO 2

The user is working in a 32Bit or 64Bit environment and receiving the error message while designing the SSIS process.

SOLUTION

The error could occur for several reasons:

• The wrong ORACLE_HOME is setup in your registry.

Use the Oracle Installer to configure your environment.

• The interactive user does not have the proper permission to access the Oracle HOME directory and it's files.

Check the permissions and configure them properly.

• Finally, verify the connection using the Oracle SQLPlus utility.

APPLIES TO

* SQL Server Business Intelligence Development Studio 2008
* Microsoft SSIS Connector for Oracle by Attunity
* Microsoft SSIS Connector for Oracle by Attunity - Itanium IA64
* Microsoft SSIS Connector for Oracle by Attunity X64

[slane]

I continue to have this problem. I've tried uninstalling and reinstalling the Oracle client, with no resolution. I'm running on 32bit environment and running into the error when trying to create a new connection. I have this set up on my 64bit server without issue, but can't set up my development environment.

I'm not sure how to fix or adjust ORACLE_HOME setup. That could be a factor since I have multiple ORACLE_HOMEs defined in my environment. Any help would be great.

Sam

[Gadi.Farhat]

Hi Sam,

Seems like the connector is trying to use the wrong ORACLE_HOME. The Oracle connection manager has an OracleHome property that can be used to force the connector working with a specific ORACLE_HOME, but unfortunately this property is not working in verision 1.0 of the connector.

Version 1.1 of the Oracle connector resolves this problem and it will be out within a few weeks.

Meanwhile, to workaround the problem, you should define a dummy registry entry (Z_SSIS) as follows:

1. Open the regedit utility.
2. Locate the following Key: HKEY_LOCAL_MACHINE\SOFTWARE\ORACLE.
3. Right-Click on ORACLE node and click on New -> Key.
4. Call the new key Z_SSIS (to make sure it's the last entry).
5. Right-Click on the Z_SSIS node and click on New -> String.
6. Name the property ORACLE_HOME.
7. Double-Click on the ORACLE_HOME and set it to the location of the Oracle installation home directory that you want to use.



Regards,

Gadi

[slane]

Thank you very much! That worked perfectly.

Sam

[Jason]

Hi,

My Production environment information are below:

OS: Windows 2003 R2 Enterprise x64 Service Pack 2
Intelligence studio: 2008
Oracle DB: 10g

I did the same steps which Gadi described:

1. install attunity 64 bit
2. install Oracle Client 11g 32 and 64 bit
3. add the new registry and reboot the server

However, there is still an error message "Failed to load oci.dll" when I try to test connecting to the Oracle DB.

I even add the permission of oci.dll to "everyone" and privilege is "Full control"!

Any help would be great!

Thanks!

[Gadi.Farhat]

Hi Jason,

You are working on x64 machine and you are getting the error in design time when you try to test the connection. In design time, the connector uses the 32-bit Oracle client because BIDS runs in 32-bit mode. You need to make sure that the correct 32-bit ORACLE_HOME is used. The 32-bit Oracle client is registered in the registery under "Wow6432Node". Therefore, you need to repeat the same steps under "HKEY_LOCAL_MACHINE\SOFTWARE\Wow6432Node\ORACL E", as described in SCENARIO 1.


Regards,

Gadi

[Jason]

Hi Gadi,

Thanks for your reply.
I follow the steps of SCENARIO 1 setting my registry but it doesn't work.
You may see the screen capture as attach file.

Would you give me other suggestion?
Thanks~

[Gadi.Farhat]

Hi Jason,

Make sure to restart BIDS. If the problem still exists, open a command line, go the Oracle client folder specified (D:\Oracle\product\11.1.0\client_32..) and try to run SQLPlus and verify that it is 32-bit application (You can see that in the Task Manager).


Regards,

Gadi

[Jason]

Hi, Gadi,

Thanks for your great help.

The root cause is that it pops up an error message when I run the 32 bit sql*plus.
The message shows that it can't find a .dll file -- msvcr71.dll.
It worked perfectly after I copied the msvcr71.dll from other server and added file to the C:\Windows\SysWOW64.

I really appreciate your great help and quick response.


Jason

[Gadi.Farhat]

Hi All,

The Microsoft Connector Version 1.1 for Oracle by Attunity, which solves the OracleHome connection manager property, is now available. You can download it from:

Download details: Microsoft Connectors v1.1 for Oracle and Teradata


Regards,

Gadi