SSIS - Execute Process Task Hangs When Run From Server Agent

I was faced with an interesting problem recently. I had a SSIS Package that was stored in the Package Store on an ETL Server, and run from a Server Agent job on a Clustered SQL Instance.

If the package was run in Debug mode, it worked perfectly. If it was run from Windows Scheduled Tasks on the Cluster, it also ran perfectly, but when run from Server Agent on the Cluster, it failed.

Or to be more accurate, it didn’t fail! In fact it didn’t do anything! It reached an execute process task that was calling a decompression utility, and sat there, not working, not failing, not doing anything!

Well, I tried the usual things, such as:

Adding SSIS Server to Local Intranet Zone

Turning on Allow Unsafe Files

Using "God Account" to run job

Using "God Account" to Run As on the execute process

Making the owner of the .exe the same account as the execution context of the process

Disable attachment manager

I knew the package and executable worked, so I was sure that it was a OS level setting, but what? Well, I struggled with this, with the aid of an infrastructure team and also MS Technical Support, but the answer finally came from the MSDN Forums, who suggested creating an Environment Variable called SEE_MASK_NOZONECHECKS and setting the Value to 1.

This worked perfectly, but it was an interesting situation, and wanted to share the resolution with you all.


Find my book, Pro SQL Server Administration on Amazon -

America


United Kingdom