I have performed a thorough search of the forums and the internet to no avail so hoping you may have some advice.
I have pgoledb driver installed on my SQL server, talking to a postgres installation quite happily.
I have a SSIS package which transfers data from postgres into SQL tables, runs absolutely fine when manually executing the package but fails to execute under SQL agent permissions.
- I have no permissions on the postgres installation, I pass the security parameters for a dedicated read only account through the SSIS package.
The SQL agent account runs under a dedicated Active Directory domain account with the correct user permissions.
The SSIS package has been built using encrypted stored credentials rather than user based to ensure the username and password aren't lost under SQL agent.
The SQL agent package appears to be defined correctly and the package password is set on the configuration.
When attempting to run under SQL agent, the package makes a call to the Postgres driver but I can see no evidence of the outgoing connection attempting to be made.
Is there any further steps required to ensure that SQL agent can correctly call and access the Postgres driver? Are there any 32bit/64bit driver considerations to be aware of, as I am already aware that the SQL agent runs packages under the 64bit driver rather than 32bit if running as a direct user?
Any help or advice appreciated