SSIS dtexec Error - The 'OraOLEDB.Oracle.1' provider is not registered on the local machine.

Question

Hi

I am have one SSIS package that connects to Oracle and pulls the data. I am trying to execute this package using DTExec utility.
"C:\Program Files (x86)\Microsoft SQL Server\130\DTS\Binn\DTExec" /ISSERVER "\SSISDB\<project>\<project_name>\<packagename>.dtsx" /SERVER "<Database_Server_name_intance>"
But I am facing oracle connectivity error "The 'OraOLEDB.Oracle.1' provider is not registered on the local machine." continuously.

I have designed this package using SSDT 2015.I have Data FLow Task, that has ADO.NET Source, it uses Orcle connection manager with Oracle OLEDB provider (.Net Providers for Olecb \Oracle Provider for OLEDB).

Problem ->
When I run this package from Visual Studio SSDT, it works fine.
When I deploy this package to SSIS DB catalog and execute it manually (Integration Service catelog ->SSIS DB -> Projects ->PAckagename.dstx -> Right Click), it works fine.
But when I try to run same package from my workstation with below command,
"C:\Program Files (x86)\Microsoft SQL Server\130\DTS\Binn\DTExec" /ISSERVER "\SSISDB\<project>\<project_name>\<packagename>.dtsx" /SERVER "<Database_Server_name_intance>"
it fails with oracle error ->
Data Flow Task:Error: ADO NET Source has failed to acquire the connection {3567A3FD-EBBB-4499-B688-CB1C2B877442} with the following error message: "The 'OraOLEDB.Oracle.1' provider is not registered on the local machine.".

What could be the issue ? I searched a lot on internet with no luck.

Current set up of my wokstation ->

1. I have Oracle Client 11.2 (32 bit) installed on my workstation.
2. All required SDK tools such as ODAC, etc are already on my workstation
3. DTEXEC command you see above belongs to SSDT 2015 (32 bit) tool installed on my workstation

Do I need to do anything else here ? Such as environment variable path, DSN, ODBC set up ?Can you please guide what is missing ?

This package runs fine with SQLAGent job as well. But I want to make this package working only with DTExec command, since I have some dependencies.

Please advise. Its very urgent.

Answer 1

Hi ,

May I know where do you run the Dtexec command? Is it in the same server as SSDT?

Best Regards,
Mona

---

If the response is helpful, please click "Accept Answer" and upvote it.

Answer 2

I am running DTExec command on my local workstation, which has SQL Server Client (SSMS 2016) and SSDT 2015 installed.

Packages (.dtsx) are deployed on another remote SSIS DB catalog server. Those packages i am executing from my local workstation DTEXec command using /ISSERVER option as below ->

"C:\Program Files (x86)\Microsoft SQL Server\130\DTS\Binn\DTExec" /ISSERVER "\SSISDB....................................dtsx" /SERVER "<SSIS_DB_CAtalog_server>"

Answer 3

Hi Sarang Deshpande,

Please try to open the CMD.exe, then run the following command:

DTExec /ISSERVER "\SSISDB\2020\Integration Services Project1\Package.dtsx" /SERVER "."  

Please refer to dtexec Utility.

Best Regards,
Mona

----------

If the response is helpful, please click "Accept Answer" and upvote it.