New Oracle Data Provider for .NET (ODP .NET) 10.2.0.2.20 failure.

Oracle recently released Oracle Data Provider for .NET (ODP .NET) 10.2.0.2.20. It happened on August 1, 2006. Oracle claims that its software is compliant with Microsoft ADO.NET 2.0 and “…more flexible, faster, and more stable….”. On top of it Oracle introduced many new features “… not available from other .NET drivers…”.

http://www.oracle.com/technology/tech/windows/odpnet/index.html

I decided to give it a shot and try in SSIS.

I installed ODP.NET on my machine and it works just fine in PL/SQL Developer

Next, I opened Data Source Wizard in BIDS for SSIS project.

I see Oracle Data Provider for .NET option is enlisted under .NET Providers node on a tree. But when I select it, I am getting an error dialog:

Failed to find or load the registered .Net Framework Data Provider.

So what could be done in such scenario?

Regards,

Yitzhak

The message seems to say the provider just cannot be found. These seems a bit extreme, but you could find out what has been registered, and try and see if the assembly is then available.

Managed providers are registered in C:\WINDOWS\Microsoft.NET\Framework\v2.0.50727\CONFIG\machine.config. There will be a section that looks a bit like this -

<system.data>
<DbProviderFactories>
<add name="Odbc Data Provider" invariant="System.Data.Odbc" description=".Net Framework Data Provider for Odbc" type="System.Data.Odbc.OdbcFactory, System.Data, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089"/>
<add name="OleDb Data Provider" invariant="System.Data.OleDb" description=".Net Framework Data Provider for OleDb" type="System.Data.OleDb.OleDbFactory, System.Data, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089"/>
<add name="OracleClient Data Provider" invariant="System.Data.OracleClient" description=".Net Framework Data Provider for Oracle" type="System.Data.OracleClient.OracleClientFactory, System.Data.OracleClient, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089"/>
<add name="SqlClient Data Provider" invariant="System.Data.SqlClient" description=".Net Framework Data Provider for SqlServer" type="System.Data.SqlClient.SqlClientFactory, System.Data, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089"/>
<add name="SQL Server CE Data Provider" invariant="Microsoft.SqlServerCe.Client" description=".NET Framework Data Provider for Microsoft SQL Server 2005 Mobile Edition" type="Microsoft.SqlServerCe.Client.SqlCeClientFactory, Microsoft.SqlServerCe.Client, Version=9.0.242.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91"/>
</DbProviderFactories>
</system.data>

This shows that I have 5 managed providers, and it also gives the strong name for each. They should then be available in the GAC. A simple application that loads an assembly by it's string name could be use dto check that it really is available.

This seems somewhat excessive, and there should be simpler explanation, but the documentation didn't make too much sense to me.

|||

I checked machine.config file on my machine.
Here is what it has:

<system.data>
<DbProviderFactories>
<add name="Odbc Data Provider" invariant="System.Data.Odbc" description=".Net Framework Data Provider for Odbc"
type="System.Data.Odbc.OdbcFactory, System.Data, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089" />
<add name="OleDb Data Provider" invariant="System.Data.OleDb"
description=".Net Framework Data Provider for OleDb" type="System.Data.OleDb.OleDbFactory, System.Data, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089" />
<add name="OracleClient Data Provider" invariant="System.Data.OracleClient"
description=".Net Framework Data Provider for Oracle" type="System.Data.OracleClient.OracleClientFactory, System.Data.OracleClient, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089" />
<add name="SqlClient Data Provider" invariant="System.Data.SqlClient"
description=".Net Framework Data Provider for SqlServer" type="System.Data.SqlClient.SqlClientFactory, System.Data, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089" />
<add name="Oracle Data Provider for .NET" invariant="Oracle.DataAccess.Client"
description="Oracle Data Provider for .NET" type="Oracle.DataAccess.Client.OracleClientFactory, Oracle.DataAccess, Version=2.102.2.20, Culture=neutral, PublicKeyToken=89b483f429c47342" />
</DbProviderFactories>
</system.data>

I also checked c:\winnt\assembly directory (GAC)
Oracle.DataAccess version 2.102.2.20 is there.

Regards,
Yitzhak

|||

There is some new development on the subject.

Oracle Data Provider for .NET (ODP .NET) 10.2.0.2.20 comes with the sample projects for Visual Studio 2005. I decided to try one of them - DSPopulate. It issues a single SQL statement and populates a data grid for Win Form app. Long story short, the sample application complies and works. References node in VS2005 shows a reference to Oracle Data provider.

I reopened my SSIS project and went through the same steps to create a new Data Source. I picked Oracle Data Provider for .NET. And here is some new behavior. Connection tab shows the following message:

Connection page is not available. Please switch to use ‘All’ properties tab page to set the properties of connection string.

So I switched to All tab and was able to set needed parameters and successfully test the connection.

I tested Oracle ODP.NET based connection with the Data Reader Source task. It works.

Regards,

Yitzhak

|||

Hi,

We also installed ODP.Net in my system however after the installation our usual oracle providers didnt worked. Started showing error when we used other oracle providers(Oracle Provider, Microsoft oracle provider).

Whether we can use ODP.NET as destination? If so, how can we use it?

Please help me in this regarding

Thanks