SSIS execute task running wscript.exe hangs when started from SQL Server Agent

Question

I have a problem with sql server 2012 SSIS package which has execute step running wscript.exe utility.

When I run this package from BIDS everything works fine, when I start package from SSIS it also starts without any problem. But once I start this package as a step in sql agent job - it hangs, and do nothing. The problem is with execute step, once I disable it in package SQL agent executes package fine.

I've tried to add SQL Agent's credentials security rights on folder with wscript.exe utility, I've also tries to add sql server proxy account. But still nothing helped.

Can you please advise how can I fix it?

UPDATE:

Once I run package in Dev Tools everythings works just fine, but running package from sql server job causes wscript.exe process hang in process list in a taskmanager. Once I manually kill this process job stops working.

I assume that this behaviour is somehow related to lack of permissions, but even new credentials didn't work for me. Or maybe wscript utility asks for some promt, though running the same script from command line didn't show any.

Answer 1

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