question

hoseinalirezabeigi-6431 avatar image
0 Votes"
hoseinalirezabeigi-6431 asked DivyeshPatel-3985 answered

oracle connection problem sql server 2019 and visual studio 2019

Hello All
I developed a SSIS package in visual studio 2019, everything worked perfectly until I had to read some data from an oracle DB, I connected to the Oracle DB with an ODBC source , from there package resulted in error in execution, by searching the errors, I found out that the 64BitRuntime should be false, trying this solution I didn't get any errors but the execution took forever and it couldn't even read a 9 row table from the Oracle DB. I started asking some of my colleagues and they said they run their package in 2017 version and it works fine, so I tested it in 2017 version and it executed without any problems so here is my first question
1. what changed from SQL server 2017 to 2019 that caused this error, or is the problem at the other end with oracle DB
after executing the package and resulting in success I created a SQL server 2019 agent job with the package. the job wouldn't execute and resulted in failure. by searching the errors found out the 32 bit runtime item should be checked, By doing so the problem was not solved and the same error would occur, having the same experience with VS2019, I created the job n SQL server 2017 and it worked just fine so here is the 2nd question
2. What is causing this, and is there a solution to run the job in SQL server 2019

the oracle database version is:

Oracle Database 11g Enterprise Edition Release 11.2.1.0 - 64 bit production
by the way ,I don't have access to this DB and I Can only access the views they made for the job .




sql-server-generalsql-server-integration-services
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

YitzhakKhabinsky-0887 avatar image
0 Votes"
YitzhakKhabinsky-0887 answered hoseinalirezabeigi-6431 commented

Hi @hoseinalirezabeigi-6431,

If you have SQL Server 2019 with SSIS, Enterprise edition, it is much better to use Microsoft Connector for Oracle. Here is the link: Microsoft Connector for Oracle

It is pretty good, and even doesn't require Oracle Client installation.




· 1
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Hi @YitzhakKhabinsky-0887
Thank you for your response and suggestion , I will use it in future projects

0 Votes 0 ·
DivyeshPatel-3985 avatar image
0 Votes"
DivyeshPatel-3985 answered

Did you resolve this issue ? if yes how was it resolved ? we have the same issue.

5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

ZoeHui-MSFT avatar image
0 Votes"
ZoeHui-MSFT answered hoseinalirezabeigi-6431 commented

Hi @hoseinalirezabeigi-6431,

Do you mean that when you run the package via VS2017, it works.

Run the package via VS2019, it failed. Could you please show the error message?

When you run in 32BitRuntime it works but some data lost?

When you run the package in SQL Sever2017, it works but fails in SQL Server 2019? Is there any error details?

Have you changed the target version when you deploy the package?

Also you may try as YitzhakKhabinsky-0887 said that use Microsoft Connector for Oracle cause it is supported since SQL Server 2019 CU1.

Regards,

Zoe


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.
Hot issues October

· 1
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Hi @Zoehui-MSFT
yes it works in 2017 versions and not in 2019 versions,
no in 2017 with 32BitRunTime it gets stuck in the ODBC source and it won't go to any further step.
In SQL server 2019, doesn't show errors it just gets stuck in the ODBC source, in SQL Agent it gives 'unexpected termination' phrase.
yes 2019 targeted version with 64bitruntime gives the 0xC0014009, 2019 without 64BitRunTime gets stuck in the ODBC source.

0 Votes 0 ·