Connecting to Oracle using ODP.Net Driver via SSIS
-
I attempting to connect and retrieve data from an Oracle DB view from SSIS. I am using the Oracle ODP.Net driver (ADO.NET:Oracle.ManagedDataAccess.Client.OracleConnection, Oracle.ManagedDataAccess, Version=4.121.2.0)). I am developing my SSIS package in Visual Studio 2015 Version 14.0.25431.01 Update 3. In my data flow I am using ADO.Net Source leveraging the ODP.Net managed driver and Data access mode: SQL command, I am able to connect and preview data and see the available fields in Design mode. However when I try to run the package in debug mode I get a validation error which states:
Error: 0xC0208449 at DFT OracleConnection, ADO NET Source [2]: ADO NET Source has failed to acquire the connection {B4AA49CB-948F-471D-9E3B-F1724D206A97} with the following error message: "Could not create a managed connection manager.".
Error: 0xC0047017 at DFT OracleConnection, SSIS.Pipeline: ADO NET Source failed validation and returned error code 0xC0208449.
Error: 0xC004700C at DFT OracleConnection, SSIS.Pipeline: One or more component failed validation.
Error: 0xC0024107 at DFT OracleConnection: There were errors during task validation.
SSIS package "MyDrive:\visual studio 2015\Projects\MySolution\MyProject\MyPackage.dtsx" finished: Failure.
I have tried running the package in 32 & 64 bit modes as most of the online advice talks about changing bit mode. It is clear I can connect I just don't have the ability while running the packages. Also I am unable to view the views in the Data access mode: Table or view. Not sure if this is some limitation of the driver.
Any assistance greatly appreciated.
-
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?
- Phil Parkin - Wednesday, June 7, 2017 1:10 PM
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.
- Mark F-428640 - Thursday, June 8, 2017 8:22 AM
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.
-
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
Viewing 5 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply