Linked PG Server SSIS package not executing under SQL Agent

Using PGNP with MS SQL Server, Data Transformation Services (DTS), SQL Server Integration Services (SSIS), Linked Servers, Snapshot and Transactional Replication and more.

Linked PG Server SSIS package not executing under SQL Agent

Postby acooke » Sun Jul 27, 2014 10:40 am

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.

    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

A
acooke
 
Posts: 3
Joined: Sun Jul 27, 2014 10:28 am

Re: Linked PG Server SSIS package not executing under SQL Ag

Postby admin » Mon Jul 28, 2014 8:57 am

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.
admin
 
Posts: 5
Joined: Wed Dec 17, 2008 6:03 pm

Re: Linked PG Server SSIS package not executing under SQL Ag

Postby acooke » Tue Aug 05, 2014 1:18 pm

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
acooke
 
Posts: 3
Joined: Sun Jul 27, 2014 10:28 am

Re: Linked PG Server SSIS package not executing under SQL Ag

Postby Moderator » Thu Aug 07, 2014 10:56 pm

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.
User avatar
Moderator
Site Admin
 
Posts: 306
Joined: Wed Oct 29, 2008 11:27 pm

Re: Linked PG Server SSIS package not executing under SQL Ag

Postby acooke » Tue Sep 02, 2014 10:39 pm

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
acooke
 
Posts: 3
Joined: Sun Jul 27, 2014 10:28 am

Re: Linked PG Server SSIS package not executing under SQL Ag

Postby Moderator » Mon Sep 08, 2014 12:28 pm

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).
User avatar
Moderator
Site Admin
 
Posts: 306
Joined: Wed Oct 29, 2008 11:27 pm


Return to SQL Server/DTS/SSIS/Linked servers/Replication

Who is online

Users browsing this forum: No registered users and 3 guests

cron