Share via

oracle connection problem sql server 2019 and visual studio 2019

hosein alirezabeigi 21 Reputation points
2021-07-28T07:26:36.82+00:00

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 Integration Services
SQL Server Integration Services
A Microsoft platform for building enterprise-level data integration and data transformations solutions.
2,702 questions
SQL Server Other
0 comments
Accepted answer
  1. Yitzhak Khabinsky 26,586 Reputation points
    2021-07-28T13:17:49.033+00:00

    Hi @hosein alirezabeigi ,

    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.

2 additional answers

Sort by: Most helpful
Most helpful Newest Oldest
  1. ZoeHui-MSFT 41,491 Reputation points
    2021-07-29T06:08:31.183+00:00

    Hi @hosein alirezabeigi ,

    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

  2. Divyesh Patel 21 Reputation points
    2021-12-20T16:56:40.88+00:00

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

    0 comments

Your answer

Answers can be marked as Accepted Answers by the question author, which helps users to know the answer solved the author's problem.