][1]
I have uninstalled and reinstalled oracle client,
I installed both 32 & 64 bit drivers to local machine,
made sure the ODBC test connection is working but I still get the error
][1]
I have uninstalled and reinstalled oracle client,
I installed both 32 & 64 bit drivers to local machine,
made sure the ODBC test connection is working but I still get the error
If you are trying to use Oracle OLEDB provider, what is the point to test ODBC connection?
They have nothing to do with each other.
I would suggest to create a udl flle, and check there what OLEDB provider is installed.
And upon selection a proper Oracle OLEDB provider, you can test the connection.
You should see something like below. I don't have Oracle OLEDB provider installed on my machine, but you should see and select Oracle's provider, and proceed to the Connection tab to test the connection.
UPDATE
Upon testing your connections successfully via udl file, you can open that udl file in notepad. It will reveal for you the entire connection string that is working. You can go now to SSIS, and compare its Oracle connection properties vs. connection in the udl file. Specially the provider attribute of the connection in SSIS. If there is a discrepancy, you can adjust it to match what is in the udl file.

I created a udl-oracle file as you suggested and the test connection was successful for oracle procider for OLE DB 
However when I try to test connection on SSIS it still fails with the error "
Test connection failed because of an error in initializing provider. The 'OraOLEDB.Oracle.1' provider is not registered on the local machine."
I updated the answer with the steps what to do next.
Check it out its UPDATE section.
Hi @PalesaMokoena-8457 ,
1.Please share the version of your SQL Server.
2.Please check if you download proper version of Microsoft Connector for Oracle.
3.Then we can create Oracle Connection Manager and test the connection.
Best regards,
Mona
If the answer is helpful, please click "Accept Answer" and upvote it.
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.
5 people are following this question.
SSIS Scale Out and its services on local group policies
Where do I download SSDT July 2016 Update (ver. 14.0.60629.0) from?
we hope Microsoft can develop databases like mysql
integration services ne figure pas dans la liste des fonctionnalités partagées de sql server
SSIS - getting data from Oracle - not working preview and metadata refresh