Blogs
Welcome to EMC Consulting Blogs Sign in | Join | Help

Welcome to EMC Consulting Blogs

SSIS Junkie

OLE DB: Cannot connect using Microsoft OLE DB Provider for Oracle

I've had a nasty little problem today and thought it was worth sharing.

I've been trying to use the Microsoft OLE DB Provider for Oracle to connect to some Oracle databases but it was failing with the error:

 

"Test connection failed because of an error in initiialising provider. oracle error occurred, but error message could not be retrieved from Oracle".

 

I was able to connect to Oracle instances just fine from normal Oracle client tools but not when using the Microsoft OLE DB Provider for Oracle - so my natural assumption was that there was a problem with my Microsoft Data Access Components (MDAC) install. I downloaded Component Checker to check out my MDAC install but it reported no problems at all - everything was as it should be.

That's when I stumbled upon this blog post: http://mobeen.spaces.live.com/blog/cns!C7EE59ADAEED6598!153.entry. Here the guy states that the problem isn't in MDAC but its in the Oracle client install which can have an incorrect setting in registry key HKEY_LOCAL_MACHINE\SOFTWARE\ORACLE\ORACLE_HOME.

I checked out my registry and sure enough I had the same problem. I made the change, attempted connection again and everything worked. Problem solved!! Praise the lord!

 

Anyone wanna complain to Larry for me? :)

 

-Jamie

 

Published 19 September 2006 23:24 by jamie.thomson
Filed under: ,

Comments

 

Strange issue connecting to Oracle « Charlie Maitland’s Blog said:

PingBack from http://charliem.wordpress.com/2006/10/10/strange-issue-connecting-to-oracle/

October 10, 2006 19:57
 

Johannes Braunias said:

Praise the Lord … I've found another URL which helped me (similar problem):

http://www.bauerfoto.com/OracleConnectFix_EnvVariable.html (set environment variable ORACLE_HOME to your ora92 directory!

April 11, 2007 11:59
 

Dibyendu said:

I had been working on this defect from morning and didnt have any idea what the problem was.... until I saw your blog. Great Work !!! Thanks

May 11, 2007 21:15
 

Chris said:

Thank you!!! Been looking for a solution for ages ...

July 6, 2007 09:23
 

Terry said:

Wow - I was working on an issue where it seemed like the only resolution was to use the MS OLE DB provider for oracle, and I couldn't get my stupid test connection to work. This fixed the problem. Just as an FYI (if you're dumb like me and don't know how to edit the registry) go to start-->run and type regedit. This will bring up the registry, you can go to the entry listed in this string. Mine originally said C:\oracle\v9201 and I changed mine to C:\Oracle\v92062 and it worked like a charm. Thanks for posting.

July 3, 2008 17:56
 

mnkhan said:

Dear All,

I have a package that connects to oracle and dumps some data into text file. It works fine till here. Now I want to get the same data from multiple oracle instances. For this reason I store the connection string in a variable which looks like this "Server=SRV1;Provider=MSDAORA.1;Pwd= PWD1;User ID= USR1". When I assign variable to ConnectionsString expresion property of Connectiono, the OLEDB source becomes invalid and give error like mentioned below. I also MsgBox the Variable and it show me the correct Username and Password.  All Gurus out there! please help me...

TITLE: Microsoft Visual Studio

------------------------------

Error at RunningRoutines [Connection manager "DXBT.APPS"]: SSIS Error Code DTS_E_OLEDBERROR. An OLE DB error has occurred. Error code: 0x80040E4D.

An OLE DB record is available. Source: "Microsoft OLE DB Provider for Oracle" Hresult: 0x80040E4D Description: "ORA-01017: invalid username/password; logon denied

".

Error at Data Flow Task [OLE DB Source [622]]: SSIS Error Code DTS_E_CANNOTACQUIRECONNECTIONFROMCONNECTIONMANAGER. The AcquireConnection method call to the connection manager "DXBT.APPS" failed with error code 0xC0202009. There may be error messages posted before this with more information on why the AcquireConnection method call failed.

------------------------------

ADDITIONAL INFORMATION:

Exception from HRESULT: 0xC020801C (Microsoft.SqlServer.DTSPipelineWrap)

------------------------------

BUTTONS:

OK

------------------------------

Thanx for your support

Najeeb

March 28, 2009 11:20
New Comments to this post are disabled

This Blog

Syndication
EMC2, EMC, and where information lives are registered trademarks of EMC Corporation.
All other trademarks used herein are the property of their respective owners.                  
© Copyright 2009 EMC Corporation. All rights reserved.                                                    
Powered by Community Server (Personal Edition), by Telligent Systems