SSIS to Oracle "Could not create a managed connection manager."

0
votes

I'm trying to use SSIS to load some data from Oracle database to MSSQL database. I created the project and used the ADO.Net source and was able to create a connection to Oracle and run queries and view results. enter image description here

However when I actually run the package I get the following error:

Error: 0xC0208449 at Data Flow Task, ADO NET Source 2: ADO NET Source has failed to acquire the connection {EECB236A-59EA-475E-AE82-52871D15952D} with the following error message: "Could not create a managed connection manager.". enter image description here

It seems similar to the issue here And I did find that I have two oracle clients version installed "11.1" and "12.2". One is used by PL/SQL and the other by other entity framework project. If this is the issue I just wanted a way to tell the SSIS to pick-up the correct one.

I tried adding Entry in machine.config for "oracle.manageddataaccess.client" section with the desired version. I also tried using other types of data sources but couldn't even create a successful connection I tried changing the Run64bitRuntime property in the project to False

Note: I don't have SSIS installed on my machine.

1
sql-serveroraclessis
which ssis version ur using – sandeep rawat
@sandeeprawat 2016 – Gubr

1 Answers

0
votes

Eventually, I just had to remove the entries related to 11.1 in path variable then restarted my machine.

Also I switched to "dotConnectForOracle" for connection and now it seems to be working fine.

I'm expecting issues related to other applications that might still be using the 11.1 version, but that will be a problem for another day.

Always make sure to write the user (oracle schema) in uppercase and some special characters [in my case it was $] in the password needs escape character even if you're using the wizard not the cmd

I still don't understand the whole issue but I hope this helps someone some day.