question

chalim-1832 avatar image
0 Votes"
chalim-1832 asked YitzhakKhabinsky-0887 commented

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

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://docs.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
image.png (14.3 KiB)
image.png (19.1 KiB)
image.png (64.2 KiB)
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
1 Vote"
YitzhakKhabinsky-0887 answered YitzhakKhabinsky-0887 commented

Hi @chalim-1832,

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.

· 2
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.

Thanks @YitzhakKhabinsky-0887 ,

Good news - my package now consistently runs successfully! I took your advice regarding the design flaw and modified the package design to be more sequential.

The bad news is that I still cannot preview the Oracle data even with the workaround you provided. After some troubleshooting, it seems like the problem is with the expression in the Oracle connection manager. When I remove the expression for ConnectionString property = @[$Project::Oracle], and then hardcode the Oracle connection manager directly, I can preview the Oracle source data. Is this a bug? Any suggestion is greatly appreciated.

0 Votes 0 ·

@chalim-1832

Good to hear that you are a making a progress.

The bad news is that I still cannot preview the Oracle data even with the workaround you provided.

It seems like a bug. Please try to make a password on the Oracle side all lower-case. And give it a shot. MS folks are entertaining an idea that the connection string expression password value is (incorrectly) converted to lower-case by the Oracle Connector.

0 Votes 0 ·
Monalv-msft avatar image
0 Votes"
Monalv-msft answered Monalv-msft commented

Hi @chalim-1832 ,

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.




· 2
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.

Thanks @Monalv-msft ,

Please see my reply to YitzhakKhabinsky-0887. I got my package to run now (yay!), but I still have problems with previewing the Oracle data when I use expressions in the Oracle connection manager.

0 Votes 0 ·

Hi @chalim-1832 ,

Could you please share the the data in parameter @[$Project::Oracle] and check if it is correct for Oracle connection string?

Please refer to Oracle connection strings and Oracle Connection Manager.

Best Regards,
Mona


0 Votes 0 ·