oracle oledb provider not registered in local machine

Hi,

I am trying to establish a connection to an Oracle database using the following code in a script task:

Dim oOleDbConnection As OleDbConnection
Dim sConnString As String = _
"Provider=OraOLEDB.Oracle;" & _
"Data Source=DBxxx;" & _
"User ID=Userxxx;" & _
"Password=Passxxx"
oOleDbConnection = New OleDb.OleDbConnection(sConnString)
oOleDbConnection.Open()

When I execute the script task, I receive the following error:
The 'OraOLEDB.Oracle' provider is not registered on the local machine.
Am using the correct provider?

I do not know how to resolve the said error.
Here are some facts:
Oracle 8i is installed.
Tnsnames.ora is updated.
I have successfully connected to Oracle SQL *Plus to test the above credentials.

Please help.
Thanks.

Do you have the Oracle OLE DB driver installed?

http://www.oracle.com/technology/software/tech/windows/ole_db/index.html|||

Shouldn't this be part of SQL Server 2005 standard installation?

I tried creating a new OLEDB connection and found in the drop down: Native OLE DB\Microsoft OLE DB Provider for Oracle.
I checked its full properties and saw provider specified: MSDAORA.1
Does this mean that the provider for Oracle is already installed?
I already tried MSDAORA.1 in my code but the same error appears.

|||

r214acc wrote:

Shouldn't this be part of SQL Server 2005 standard installation?

I tried creating a new OLEDB connection and found in the drop down: Native OLE DB\Microsoft OLE DB Provider for Oracle.
I checked its full properties and saw provider specified: MSDAORA.1
Does this mean that the provider for Oracle is already installed?
I already tried MSDAORA.1 in my code but the same error appears.

The Microsoft OLE DB Provider for Oracle is not the same as the Oracle OLE DB Provider. The Oracle OLE DB Provider is published by Oracle, not Microsoft, and isn't part of the SQL Server 2005 standard installation. I know this because I'm running SQL Server Standard on one of my machines.|||So, like I said, try downloading the Oracle OLE DB driver, and then try your code.|||

Hi Duane,

You are right. However, I tried both MS OLE DB Provider for Oracle and Oracle OLE DB Provider but the error still persists.
Is it possible that Oracle 8i (on the server running SSIS) does not have the components to be used by the SQL Server 2005? or can it connect to a Oracle 9i 64-bit?

Please help me find the answer. Thanks.

|||

r214acc wrote:

Hi Duane,

You are right. However, I tried both MS OLE DB Provider for Oracle and Oracle OLE DB Provider but the error still persists.
Is it possible that Oracle 8i (on the server running SSIS) does not have the components to be used by the SQL Server 2005? or can it connect to a Oracle 9i 64-bit?

Please help me find the answer. Thanks.

Sorry, I don't have an answer for your question. However, I have a question for you. Are you using the 64 bit version of SQL Server 2005?|||

I am using 32 bit version of SQL Server 2005.
Is this the cause of the problem?

|||

There are some issues with Oracle drivers on 64 bit, hence Duane asked.

You say Oracle and SQL with SSIS are installed on the same server. That is fine, but are you really working on the server itself or a desktop?

|||

Hi,

I am working on the server itself.
I found something in the forum that may be related to the problem:
http://forums.microsoft.com/TechNet/ShowPost.aspx?PostID=449593&SiteID=17
It says that there is a bug on parsing folder names with parenthesis which happens when a 64-bit OS installs 32-bit programs.
Since I am running a 32-bit SSIS. This may be the problem.

Is there a fix here?
Thanks.