<

How to fix OLE DB connection error while connecting Oracle database using SSIS

Published on
3,064 Points
64 Views
Last Modified:
Editors:
In this article, we will see how we can fix the OLE DB connection error when connecting to an Oracle database using SQL Server Integration Services.

SQL Server Integration Service (SSIS) is a Microsoft product which is mainly used for automating importing and exporting data or Extract, transform and load i.e. ETL.  Here is the link to Integration services tutorials by Microsoft.


Open Visual Studio 2017, once started you need to create a project or solution under integration services project.



Once your new project is started, navigate to Connection Manager as shown in below screen.



Now right click on the options to create new OLE DB connection or a new connection to select connection manager for Oracle.



Now select the provided as "Native Microsoft OLE DB provider for Oracle" options from the list of values. And provide the service details, username, and password of the Oracle database that you want to connect to.  You can also try to connect using Oracle.



After providing the details click on Test Connection to verify if the connection was successful or not.



We can see the error that we are seeing upon clicking on Test Connection. To fix the error we need to install Oracle Client 32bit software that is provided by Oracle. You can find all the software’s provided by Oracle from this link.


I have already downloaded and extracted the Oracle client on my machine. So now let us install the software first.



Now to start the installation run the setup application to initiate the installation process.



Click Next to continue and follow the default steps.




It might ask for the >NET Framework for installing the client so make sure you complete the install if required. And this might take you to restart your system which will finally install the client on your machine.  Once the installation is completed, copy the TNS details from your database to the Admin path of the Oracle client that is installed.



Copy the TNS.ora file to the client path as shown below.



Now let us follow the steps that we have taken initially to connect Oracle database from SSIS and you will see that you can connect to Oracle through OLE DB Microsoft native provider or by using Oracle connection manager.



Finally, we are able to connect to Oracle database successfully. Note if we want to make ETL transfer faster to Oracle or to read the date then try to use " Attunity connectors" a.k.a.  SSIS connections.


After installing the connectors, you will find the Oracle source and destination on the common toolbox as shown below:


 

Thank you for reading my article, please feel free to leave me some feedback or to suggest any future topics. I'll be looking forward to hearing from you – Swadhin Ray (Sloba)

 

For more information about me, please check out my Experts Exchange Profile page.

 


0
Author:Swadhin Ray
Enjoy this complimentary article view.

Get unlimited access to our entire library of technical procedures, guides, and tutorials written by certified industry professionals.

Get 7 days free
Click here to view the full article

Using this article for work? Experts Exchange can benefit your whole team.

Learn More
COLLABORATE WITH CERTIFIED PROFESSIONALS
Experts Exchange is a tech solutions provider where users receive personalized tech help from vetted certified professionals. These industry professionals also write and publish relevant articles on our site.
Ask questions about what you read
If you have a question about something within an article, you can receive help directly from the article author. Experts Exchange article authors are available to answer questions and further the discussion.
Learn from the best.