CODEWITHSUNDEEP
oracle-databasessissql-server-2019
BIDeveloper
BIDeveloper

Reputation: 11

SSIS Oracle Source - DTS_E_CANNOTACQUIRECONNECTIONFROMCONNECTIONMANAGER

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,

  1. In VS2019 Run64BitRuntime to false
  2. In agent job, checked it to run in 32bit mode
  3. 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

Upvotes: 0

Views: 662

Answers (1)

BIDeveloper
BIDeveloper

Reputation: 11

I finally got it sorted. Was given very useful tips from a professional on Microsoft forums.

I used the EZConnect (to avoid using TNS names which was problematic on the server) method for Oracle in the connection manager. Kept the SQL Server 2019 to the latest build. Removed the 32-bit and installed the 64-bit MS Oracle Connector on the SQL Server and it worked.

Upvotes: 1

Related Questions