Sql-server – SSIS Execute Sql Task not executed on server

Without some detail of error messages it is hard to know for sure what's going wrong, however it sounds like it may well be either a permissions issue or a problem running the script on that host.

When an agent job runs, it will run under the agent service account or the credential used to create the proxy that the step runs as.

This means that the account will need permissions on wscript.exe and the script that wscript.exe runs. I think my next step would be to do the following test:

1. create a new credential using your login
2. use this credential to create a proxy
3. run the step of the agent job as this proxy

If this doesn't work, try running the script directly on the host where SQL Server is running - the outcome of this would indicate if there is an issue with the script being able to run from that host

I could not resolve this issue and changed my approach to utilize a SQL Job instead of running the package directly from a powershell script.

Summary of solution:

• SQL Job with one Job Step
• The Job Step executes the SSIS package

To run a SQL Job with a custom you must also create:

• an instance-level Credential
• a Proxy Account which should be mapped to the Credential

I still wonder what's different about this approach versus the 'interactive' approach listed above. Both approaches run with the exact same identity and privileges!

For an in-depth description of the steps required to configure a SQL Job, look at this example code for Automated Deployment of a SQL Job and Job Step.

Hope this helps someone else in the same situation -