In this blog, 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 softwarethat 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 blog, please feel free to leave me some feedback or to suggest any future topics.