Error when targeting SQL Server 2017 after upgrading SSIS using Attunity for Oracle to VS 2019 , SSIS 3.10 using MS Connector for Oracle

Question

The issue is related to the one I have asked before and logged under :
https://learn.microsoft.com/en-us/answers/questions/170084/error-after-upgrading-ssis-using-attunity-for-orac.html

Visual Studio 2019 Version 16.7.4
SSIS extension : 3.10
Targeted server version for Deployment : 2017 .
Installed Microsoft Connector for Oracle V 1.0
Installed as well Microsoft Connectors v.5.0 for Oracle both 86 and 64.

When targeting server version 2019 for deployment it works but only when using EzConnect format: [//]host[:port][/service_name]

When targeted server version 2017, I'm testing first the Oracle connection manager which uses the same EzConnect format and it succeeds ( Connection successful).
If I change to the old connect descriptor name defined in the tnsnames.ora file - as used in previous VS versions - it fails.

But the issue is that when executing the package I get the following run time error from the start :

The AcquireConnection method call to the connection manager MSORA failed with error code 0x80004005. There may be error messages posted before this with more information on why the AcquireConnection method call failed.

And further below : Error: OCI error encountered. ORA-12154: TNS:could not resolve the connect identifier specified.
That's look strange to me, as the test using the Connection manager is successful.

Thank you very much for any ideas that can help me solving this issue.

Answer 1

HI @Anonymous ,

Microsoft Connector for Oracle V 1.0 can be used in SSIS while targeting MS SQL Server 2019 Enterprise edition only.
Sorry to disappoint you, you cannot use it for SQL Server 2017.

But if we exclude this, one may expect that when targeting SQL Server 2017, the MS Connector for Oracle will be disabled accompanied by at least a warning message as it is useless.
Am I missing something here ?

You are absolutely right. The MS Connector for Oracle messages are misleading, and should be improved.

Microsoft Connector for Oracle V1.0 is a minor release.

It is a major release:

• It cuts off dependency on Attunity.
• No need to install Oracle Client, Oracle Home(s), TNSes.

Answer 2

Hi @Anonymous ,

1.Installed Microsoft Connector for Oracle V 1.0

Microsoft Connector for Oracle V1.0 is a minor release. It supports SQL Server 2019 Integration Services and includes bug fixes and support for updated Oracle product releases.

Please refer to Microsoft Connector for Oracle V1.0 .

2.And further below : Error: OCI error encountered. ORA-12154: TNS:could not resolve the connect identifier specified.

Please refer to ORA-12154: TNS:could not resolve the connect identifier specified.

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.
Hot issues in November--What can I do if my transaction log is full?
Hot issues in November--How to convert Profiler trace into a SQL Server table?

Answer 3

Thank you @Yitzhak Khabinsky and @Monalv-MSFT for your answer.

So what is your suggestion when targeting SQL Server 2017 ?
Mine is replacing the Microsoft Connector with an Oracle Provider for Ole DB and use it by an OLE DB Source component.
I've just used this and executed successfully.

But if we exclude this, one may expect that when targeting SQL Server 2017, the MS Connector for Oracle will be disabled accompanied by at least a warning message as it is useless.
Am I missing something here ?

Thank you.