Linked PG Server SSIS package not executing under SQL Agent

More
6 years 1 week ago #11171 by acooke
acooke created the topic: Linked PG Server SSIS package not executing under SQL Agent
Hoping you can help.

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.

[list:1uhypzv6]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.[/list:u:1uhypzv6]

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

A

Please Log in or Create an account to join the conversation.

More
6 years 1 week ago #11172 by admin
admin replied the topic: Re: Linked PG Server SSIS package not executing under SQL Ag
Please check if EventsLog/SQLServer logs contain any error messages. Also, please run PGNP Profiler as Administrator, and see if any errors are in the trace.

The issue might be caused by lack of permissions. The SQL Agent account should be granted permissions to access the hidden folder C:\ProgramData\Intellisoft.

Please Log in or Create an account to join the conversation.

More
6 years 1 day ago #11240 by acooke
acooke replied the topic: Re: Linked PG Server SSIS package not executing under SQL Ag
Hi

Thanks for the advice. The account running the SQL agent services has full access to the directories as recommended, with no luck unfortunately.

I have run the PGNProfiler as administrator whilst attempting to run the SQL agent packages, an entry does appear for DTExec when attempting to execute, but there are no entrires in the log and the package does not progress.

Again, the SSIS packages is configured not to store sensitive data and instead uses SSIS package configuration files, a process that works perfectly if executed manually.

Regards

A

Please Log in or Create an account to join the conversation.

More
5 years 11 months ago #11269 by Moderator
Moderator replied the topic: Re: Linked PG Server SSIS package not executing under SQL Ag
Could you (temporarily) grant full permissions to Everyone on the C:\ProgramData\Intellisoft folder? I want to exclude one of the possible reasons of the issue. My recollection is that similar issue occurred in the past on Terminal Services server.

Please Log in or Create an account to join the conversation.

More
5 years 11 months ago #11892 by acooke
acooke replied the topic: Re: Linked PG Server SSIS package not executing under SQL Ag
Hello

I have granted full permissions to the everyone group to the requested folder with no success. This includes a server reboot to ensure permissions have been fully picked up.

Regards

A

Please Log in or Create an account to join the conversation.

More
5 years 10 months ago #12026 by Moderator
Moderator replied the topic: Re: Linked PG Server SSIS package not executing under SQL Ag
Could you turn detailed trace in the PGNP Profiler? In the main log window right click mouse and select "Options...". In the Options dialog change Trace Level to "Comment Level 3", click OK. Profiler restart might be needed. Then execute the SQL Agent task. Please send the debug messages to us (support at pgoledb).

Please Log in or Create an account to join the conversation.

More
5 years 8 months ago #12665 by acooke
acooke replied the topic: Re: Linked PG Server SSIS package not executing under SQL Ag
Hello

Apologies for the delay in response, I have set the detailed trace in the PGNP profiler to comment level 3 and conducted the necessary restart.

Unfortunately once again when initiating the job from SQL Agent, within PGNP profiler an entry does appear for DTExec when attempting to execute, but there are no entries in the log and the package does not progress. It appears not to be attempting to call the driver or attempt connection.

Again, if executing the package manually through SQL agent under my own credentials it appear to run, it is only when running under SQL Agent in an scheduled state that i appear to have problems.

Thoughts are possibly:
Issues when running under SQL agent account, which driver does it use, 32bit vs 64 bit??
Does the domain account running SQL agent require any specific further permissions/configuration to call the driver in an unattended state?

Any suggestions appreciated

Regards

A

Please Log in or Create an account to join the conversation.

Time to create page: 0.062 seconds
Powered by Kunena Forum