SSIS dtexec Error - The 'OraOLEDB.Oracle.1' provider is not registered.
-
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 Oracle 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.
-
Is that Oracle provider a 64-bit piece of software?
Because it looks like you are calling the 32-bit version of DTExec.
Try making the same call, but with the 64-bit version.
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin -
Hi Phil
Thanks for your response. but how to download SSDT 2015 64 bit version ? I searched everywhere, its not available.
Can you please provide link, if you are aware ?
- sarang1183 wrote:
Hi Phil
Thanks for your response. but how to download SSDT 2015 64 bit version ? I searched everywhere, its not available.
Can you please provide link, if you are aware ?
SSDT does not have a 64-bit version.
Is there a DTExec.exe version under Program Files?
Try looking here:
C:\Program Files\Microsoft SQL Server\140\DTS\Binn
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin -
Hi Phil
There is no DTExec under Program files. Also I dont have any folder such as "C:\Program Files\Microsoft SQL Server\140\DTS\Binn".
I read somewhere that if you install SSDT 2015 on 64 bit Workstation, both the versions of DTExec (32 bit as well 64 bit) are automatically installed under C:/Program files (x86) and c:/Program Files, but this did not happen in my case.
-
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.
Regarding this comment, is this running from your machine? That is, do you have SQL Server installed locally? If not, you cannot run SSIS packages outside of SSDT, as far as I know.
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin -
Hi Phil
SSIS DB catalog + database (tables stored proc) are on different Server from my workstation. MY workstation only has SSDT 2015 and SSMS 2016.
To answer your question,"Regarding this comment, is this running from your machine? That is, do you have SQL Server installed locally?", --------> yes DTExec command is running from my machine. No local SQL Server installed.
"If not, you cannot run SSIS packages outside of SSDT, as far as I know." ---------> I am able to run all other SSIS packages which are connecting to SQL Server. I am facing this issue only for package which is trying to connect to Oracle.
Are you saying that, to get rid of this error, SSIS DB Catalog, SSDT 2015 and Oracle client all should on same workstation ?
-
To be honest, I'm running out of ideas, so I decided to go back to basics.
You are saying that you can run packages from the command line using the DTExec that was installed with your version of SSDT, despite not having a licensed version of SQL Server installed, is that correct? I did not think that it was possible to do this.
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin -
Hi Phil
Yes. On my current workstation, I have installed SSMS 2016 (Sql Server Management Studio, free download) and SSDT 2015 (free download). As per my understanding, DTExec becomes available in "C:\Program Files (x86)\Microsoft SQL Server\130\DTS\Binn" once we install SSDT 2015 on any workstation., is it correct ?
I deployed .dtsx packages from my SSDT 2015 to SSIS DB Catalog , which is on another database server.
Using DTExec, I am trying to execute .dtsx packages deployed on a above SSIS DB catalog database server.
Was I able to answer your question ?
-
The fact that DTExec gets installed by SSDT does not necessarily mean that it will run from the command line.
If you run the DTExec which is on the SQL Server instance, from the command line, does that work?
If DTExec worked from the command line on any PC which had SSDT installed, Microsoft would essentially have given away SSIS for free.
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin -
When you say SQL Server Instance, what does it mean ? SQL Server instance having SSIS DB catalog installed on it (where packages are deployed ) OR just SQL Server Client (SQL Server Management Studio) ? Because along with SSDT installed, I have just SQL client SSMS and not complete SQL Server engine on my workstation, still I am able to run DTEXec command. To answer your question, yes, DTExec command works from command line.
I successfully ran DTExec from my command line. I have attached 2 screenshots for your reference. As per "1 DTExec_comand_works.jpg", I am able to execute the package successfully, but when i look at catalog report "2 Oracle_Error_in_catalog_report.png", i can see the package failed due to oracle issue.
Since DTExec command utility has way to execute packages deployed on remote SSIS DB catalog server, i have been successfully able to execute all other .dtsx packages deployed on remote SSIS DB server, which do not connect to oracle. Only for the packages which connect to Oracle , I am getting error, and I am trying to find out whats missing on my workstation (oracle drivers, other setup etc)
I dont have any issues running DTEXEc from command line of my workstation, issue is the next step after this package is executed. When package is trying to connect oracle, after that i am getting oracle error.
I hope i am able to provide you correct information and not confusing you 🙂
-
OK, let's backtrack a little.
You can run the package from SSDT and it works fine.
In SSDT, try going to Project/Properties/Configuration Properties/Debugging and setting Run64BitRuntime to false.
Run the package. Does it work, or do you get an error?
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin -
Hi Phil
I have already tried that option . But it did not help. I still keep getting same Oracle connection error.
I did the change you suggested, compiled/ deployed the package and then ran DTExec command, but no luck.
-
on your SERVER you need to install the Oracle client components and configure it as required (tnsnames, sqlnet.ora) - as your SQL server is 64 bit you should install the 64bit version of the client (ODAC/InstantClient/full client - your pick)
once it is installed it will show up on SSMS->Server Objects -> Linked Servers ->Providers
or with t-sql by running
EXEC sp_MSset_oledb_prop
if the oracle provider does not show up on the list above then your SSIS packages will fail with the error you have.
regarding your use of dtexec on your workstation - you most likely have installed a version of SQL Server on your workstation as dtexec checks for an installation and prevents normal execution of SSIS packages outside Visual studio if SQL Server Integration Services is not installed. Although it is possible that executing on the catalog bypasses this validation but I don't have a way to test that at the moment.
installing SSDT (and/or SSMS) does not grant permission to execute DTEXEC on the command line as this executable is under the licensing of a SQL Server install (or developer license))
- sarang1183 wrote:
Hi Phil
I have already tried that option . But it did not help. I still keep getting same Oracle connection error.
I did the change you suggested, compiled/ deployed the package and then ran DTExec command, but no luck.
You misunderstood.
a) You said that you can execute (not deploy) the package from SSDT, correct?
b) If yes, try executing from SSDT after changing the Run64BitRuntime setting.
Run64BitRuntime only applies within SSDT. It does not affect deployed packages.
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin
Viewing 15 posts - 1 through 15 (of 23 total)
You must be logged in to reply to this topic. Login to reply