VS2019 SSIS Intermittent Oracle Connection Failure during Execution + Cannot Preview Oracle Source Data

chalim 1 Reputation point
2021-04-10T05:12:59.973+00:00

Hello,

I have an ETL SSIS package that works for SQL Server 2012/Oracle 12c and uses the Attunity connector. It is using the Package Deployment model. However, I am trying to migrate the SSIS packages to use SQL Server 2019 and Oracle 12c and the new Microsoft Connector for Oracle V1.0 for Visual Studio 2019. In my old post (https://learn.microsoft.com/en-us/answers/questions/319381/vs2019-oracle-connector-not-working-with-ssis-pack.html), it was recommended that I use the Project Deployment Model, so I am trying to convert to that model. The conversion wizard seemed to do a pretty good job. However, sometimes the package runs successfully and most of the times, it fails. I have about 100 tables and when it does fail, it does not fail on the same Oracle table. The intermittent error is:

"[Oracle Source [61]] Error: SSIS Error Code DTS_E_CANNOTACQUIRECONNECTIONFROMCONNECTIONMANAGER. The AcquireConnection method call to the connection manager "Oracle" failed with error code 0xC0014009. There may be error messages posted before this with more information on why the AcquireConnection method call failed"

At the very end of the execution results is this error:
"[Connection manager "Oracle"] Error: There was an error trying to establish an Open Database Connectivity (ODBC) connection with the database server."

  • When I test the Oracle connection manager, it is successful.
  • Delay Validation is set to True for all the Connection managers.
  • Solution Protection level is set to DontSaveSensitive.
  • Solution Run64BitRuntime is set to False.

My setup:
Microsoft SQL Server 2019 (RTM-CU9) (KB5000642) - 15.0.4102.2 (X64) Developer Edition (64-bit)
Visual Studio Professional 2019 (Version 16.9.1)
Oracle 12c (AWS RDS instance)
Microsoft Connector for Oracle V1.0 32-bit (version 2019.150.2000.110)
SQL Server Integration Services Projects extension version 3.12.1 (downloaded from VS2019 marketplace)
Microsoft OLE DB Driver for SQL Server (64-bit)

Project parameters:
86444-image.png

  1. I am not sure if I need the Microsoft Connector for Oracle 32-bit or 64-bit, but I have tried them both. How do I know which one I need?
  2. I have the 100 tables divided into 4 sequence containers (about 20-25 data flow tasks in each container) and it has worked fine in the past. Also, the tables are not large - this is just test data for now and the largest table has 30,000 records. Not sure if this could be a problem with VS2019?
  3. When I try to preview any of the Oracle source data, it gives me a nasty error. Please see screenshot below.
    86390-image.png
  4. When I try to create a new Oracle source, my Oracle connection manager is selected in the dropdown by default which is good, but then when I try to select a table from the subsequent dropdown, it is not able to show me a list of tables and it throws an error.
    86408-image.png

Any help is much appreciated!

SQL Server Integration Services
SQL Server Integration Services
A Microsoft platform for building enterprise-level data integration and data transformations solutions.
2,451 questions
0 comments

2 answers

Sort by: Most helpful
Most helpful Newest Oldest
  1. Yitzhak Khabinsky 24,941 Reputation points
    2021-04-11T01:22:55.813+00:00

    Hi @chalim ,

    I will try to answer your questions ##1-4.

    1. Set your VS solution Run64BitRuntime to True.
    2. Your SSIS package design has a major flaw. Each Sequence container and its multiple DFT tasks are executed in parallel. That parallelism is dependent on a number of logical proccessors on a machine. So it creates a situation where it is impossible to open a new CPU thread for a DFT. You need to create a process flow inside each Sequence container to create a sequential execution for DFTs.
    3. Don't know what is causing that.
    4. It is a bug with the Microsoft Oracle Connector. It shows an irrelevant OLEDB error dialog box. I already opened a ticket for it with the MS Premium Support. Though there is a workaround for the bug. Go to Advanced Editor =>Component Properties and set the Oracle view or table name in 'TableName' field directly. Whatever you type is case sensitive, and shall match what is on the Oracle side.
    1 person found this answer helpful.
  2. Monalv-MSFT 5,891 Reputation points
    2021-04-12T07:32:46.887+00:00

    Hi @chalim ,

    1.Error: SSIS Error Code DTS_E_CANNOTACQUIRECONNECTIONFROMCONNECTIONMANAGER
    Please delete the connection manager and then create new connection manager.

    2.Please download Microsoft Connector for Oracle V1.0 64-bit and set Run64BitRuntime as True.

    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.