Sql-server – SSIS package deployed to SQL Server hangs when running a Powershell script

powershellsql serversql-server-2012ssis

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

Best Answer

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.

Related Question