SSIS Oracle Source - DTS_E_CANNOTACQUIRECONNECTIONFROMCONNECTIONMANAGER

Deepak 101 Reputation points
2021-04-08T14:33:05.567+00:00

Hi All,

I'm using Visual Studio 2019 to get data from a Oracle Database and transfer it to a SQL 2019 database. I'm using Oracle connection (Connection Manager for Oracle Connections) for my source and an OLE DB connection for my SQL 2019 database. Test connection work fine for both and when I execute the package in VS2019, it works fine.

When I deploy it to SQL2019 and run it from a agent job, I get a DTS_E_CANNOTACQUIRECONNECTIONFROMCONNECTIONMANAGER error. I have seen some forums and done following changes,

In VS2019 Run64BitRuntime to false
In agent job, checked it to run in 32bit mode
Installed MicrosoftSSISOracleConnector on the server containing SQL2019

Tired by setting DTS property to EncryptSensitiveWithUserKey and with password too.
Also tried DelayValidation with True and with False.

But no joy so far. Has anyone troubleshooted this in past and can shed some light on this please?

Deepak

SQL Server Integration Services
0 comments

2 answers

Sort by: Newest
Most helpful Newest Oldest
  1. Monalv-MSFT 5,926 Reputation points
    2021-04-09T08:25:54.31+00:00

    Hi @Deepak ,

    May I know if you download the oracle connector for both 32bit and 64bit?
    86089-oracleconnectorboth32bit-64bit.png

    Please refer to Microsoft Connector for Oracle.

    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.

    Was this answer helpful?

  2. Deleted

    This answer has been deleted due to a violation of our Code of Conduct. The answer was manually reported or identified through automated detection before action was taken. Please refer to our Code of Conduct for more information.

    11 deleted comments

    Comments have been turned off. Learn more

Your answer

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