I have an SSIS package that is loaded onto the a sql server. The package fails when executed as a sql agent job, but the package runs fine when the user goes into the ssisdb catalog.
The job is accessing two different databases, one sql server instance using integrated security and one oracle database. The job step is being run as a proxy user that has access to the sql server database. The oracle connection string has the server name and oracle service name, as well as the oracle username and password.
When the job runs via the agent it fails with the following error
” An OLE DB error has occured. Error code: 0x80004005.
An OLE DB record is available. Source: “OraOLDEDB”.
Hresult: 0x80004005 Description: “ORA-12154: TNS:could not resolve the connect identifier specified”
This strikes me as odd because when specifying the servername and oracle service name, the tnsnames.ora file should not be needed. This also seems like an unlikely root cause, because the package runs fine when the developer executes the package from the ssisdb catalog. To further rule this out, I changed the connection string from server/service to just the database name found in the tnsnames.ora file. I verified the name is resolved via tnsping, which works successfully.
I am beginning to think that the error is being caused because the proxy account can not access the oracle client, so I tried to make the proxy account a local admin on the sql server, where the oracle client is installed. This didn’t change anything.