Hello Experts,

We have a SSIS package that connects to oracle database and pulls data for load into SQL Server.

This package is configured in SQL Agent Job to run on schedule. But while it is executed from the SQL Agent it fails with error message: 

ssis error code dts_e_cannotacquireconnectionfromconnectionmanager. The acquireConnection method call to connection manager Oracle Conn failed.

When I run this package locally or from the SSIS instance of server it executes successfully. Also protection level set for this package is set to "EncryptSensitivewithPassword".

TNSNAMES.ORA file on server also has entry for this connection.

Please provide suggestions to help me resolve this issue.

Thanks

The account that owns the step executing the package and the account running the SQL Agent service should have access to the Oracle database.

The error message in your post suggest that they don't (in particular, the account owning the step).

Hi Vijay,

Did you loaded/moved the TNS names file to the correct path on server?

It should be at the same location as it is on your local/test server.