Connecting to Oracle using ODP.Net Driver via SSIS

Question

Connecting to Oracle using ODP.Net Driver via SSIS

Viewing 4 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

Phil Parkin SSC Guru Points: 247183 More actions · Answer 1

It seems that many others have experienced similar issues. One method which appears to have worked for some is using an ODBC connection rather than ADO.NET. Is this a possibility for you?

Mark F-428640 Hall of Fame Points: 3659 More actions · Answer 2

Phil Parkin - Wednesday, June 7, 2017 1:10 PM
It seems that many others have experienced similar issues. One method which appears to have worked for some is using an ODBC connection rather than ADO.NET. Is this a possibility for you?

I am having issues with the connection string format for this object would you happen to know the correct format for this or maybe you have a link to one of the other folks who have had success? I will continue to search in the meantime - thanks for you reply.

Phil Parkin SSC Guru Points: 247183 More actions · Answer 3

Mark F-428640 - Thursday, June 8, 2017 8:22 AM
Phil Parkin - Wednesday, June 7, 2017 1:10 PM
It seems that many others have experienced similar issues. One method which appears to have worked for some is using an ODBC connection rather than ADO.NET. Is this a possibility for you?
I am having issues with the connection string format for this object would you happen to know the correct format for this or maybe you have a link to one of the other folks who have had success? I will continue to search in the meantime - thanks for you reply.

Not specifically. However, if you create an empty UDL file (call it conn.UDL, perhaps) and double click on it, you should be able to create a connection and save its details to the file. After saving the connection, open the UDL file using a text editor & the connection string should be there.

Mark F-428640 Hall of Fame Points: 3659 More actions · Answer 4

Hi Phil,

Thanks for your responses the following is the resolution to my connectivity issue with some additional details.

The Oracle Client was not installed on my machine. After installing the 32 bit Oracle 12g client the VS Oracle drivers became available which were able to connect and retrieve data from the Oracle DB. The following notes are related to what I found after trying several of the Drivers. I am planning to develop using the .Net OracleClient Data Provider.

ADO NET Source:

Using the .Net Providers\ODP.NET, Managed Driver I am able to connect in design mode and view both fields and preview data without issue. Also able to run in debug mode for both 32 & 64 bit modes and view data without issue. However now editing the connection manager crashes Visual Studio with following message:

Using the .Net Providers\ODP.NET, Unmanaged Driver I am able to connect in design mode and view both fields and preview data without issue. Also able to run in debug mode for both 32 & 64 bit modes and view data without issue. No other issues found.

Using the .Net providers\OracleClient Data provider I am able to connect in design mode and view both fields and preview data without issue. Also able to run in debug mode for both 32 & 64 bit modes and view data without issue. No other issues found.

OLE DB Source:

Using the Native OLE DB\Oracle Provider for OLE DB I am able to connect to the views and see the columns however a warning is thrown prior about inability to retrieve the column code page.

When I try to preview the data the same warning is displayed above and then the following error is thrown:

I am able to execute the query and see results in the data viewer running in 32 & 64 bit debug mode.

Using the Native OLE DB\Microsoft OLE DB Provider for Oracle I am able to connect to the views and see the columns however when I try to preview the data the following error is thrown:

I am able to execute the query and see results in the data viewer running in 32 & 64 bit debug mode.

Regards,

Mark