SQL Agent Job - SQL server 2012

Hi,

I have created a SSIS package that transfers data from flat files in to SQL tables and then in to Dynamics GP via an E-One SmartConnect map (creates receiving’s transactions).

When we run the package from BIDS there are no problems and executes correctly. However, when we deploy the package to SQL Server Agent and run the job via a “scheduled task” or “Start Job at Step” the job fails with the following error message

“There was no endpoint listening at net.pipe://localhost/Microsoft/Dynamics/GP/eConnect/EntityOperations that could accept the message. This is often caused by an incorrect address or SOAP action. See InnerException, if present, for more details”

The package is run using a proxy user which is linked in to Security Credential.

The same account is being used whether we run the job inside or outside of the SSIS package.

This sounds like a permission issue but I am unable to work it out, can anyone help?

Thanks

It's related to permission of job owner and sql agent service account. Can you tell who's job owner, what kind of sql permission it has, what's sql agent service, what kind of sql and OS permission it has, where's flat file, ...?

Hi rmiao,

Thanks for the reply, please my responses below

1. Can you tell who's job owner? Yes, it is the same account as the proxy that is being used to run the SSIS package

2. What kind of sql permission it has? The account has sysadmin

3. what's sql agent service? What kind of sql and OS permission it has

NT SERVICE\SQLAgent$MGP
Server roles = Public
sysadmin

4. Where's flat file? Flat File is on a different server which is transferred in to the SQL database on the server where the job is running, I have copied the job and set it up to only run the transfer task using a single table from a SQL database and it still has the same issue

Did you try put flat file on sql server's local disk and let ssis get that local file instead of transfer? Does sql agent service account have proper permission to use Dynamics GP?