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:
- 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?
- 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?
- When I try to preview any of the Oracle source data, it gives me a nasty error. Please see screenshot below.
- 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.
Any help is much appreciated!