SSIS package deployed to SQL Server hangs when running a Powershell script

Question

We have a SSIS package that tidies up a few files and folders, and want to automate it to run every night via the SQL Server Agent.

When running the package via Visual Studio (2015 and 2017) the package completes successfully. When the package is deployed to SQL Server 2012, it never gets past the element that runs a Powershell script - it simply hangs forever (3 days is the record so far).

SSIS data flow:

Data Flow

"Run Compression PowerShell Script" task:

Execute Process Task

The Powershell script is very basic - two lines of code:

$filePathName = $args[0];
Compress-Archive -Path $filePathName -DestinationPath $filePathName

We think it may be a permissions issue, but we're not sure where to progress this further.

Any help is gratefully received.

Thank you

• HandyD: If you change the step from running cmd.exe with /C PowerShell to running powershell.exe does it function as expected?
• BishNaboB: @HandyD - Have tried this and still nothing.
• HandyD: Can you change it from an Execute Process task to a Script Task and use C# to compress the file instead?
• BishNaboB: @HandyD Whilst I can do this, it doesn't fix the issue unfortunately. Plus I have another package with the same issue with another PowerShell script that alters large files that are seemingly too large for a C# script to handle.

Answer 1

The SSIS element was a red herring. The issue was the execution policy being passed to PowerShell when running the script.

Changing from

-ExecutionPolicy Unrestricted

to

-ExecutionPolicy ByPass

has resolved the issue.

Answer 2

Assuming you're running the package via a SQL Server Agent job, are you running the step as the SQL Server Agent service account or a custom proxy? That will determine the context by which the PoSh script will run.

Also, under the Execution Options tab for the SSIS job step, are you running with 32-bit or 64-bit mode enabled? This will be set for the architecture of the SQL instance, while Visual Studio may be a 32-bit installation.

• BishNaboB: The job is being run as the SQL Server Agent service account. I'm pretty sure the package is 64bit on Visual Studio and on the SQL server, but I will check this tomorrow.
• BishNaboB: I've checked - 64bit mode.
• MattyZDBA: I'd try first flipping the switch to 32-bit mode to address the unknowns regarding the architecture. If this fails, create a credential utilizing the domain account you used in developing the package and map it to a new SSIS proxy in SQL Server Agent, reconfiguring the step to run with the proxy.