2

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

Improve this question
4
  • If you change the step from running cmd.exe with /C PowerShell to running powershell.exe does it function as expected?
    – HandyD
    Sep 7, 2018 at 5:40
  • @HandyD - Have tried this and still nothing.
    – BishNaboB
    Sep 7, 2018 at 13:28
  • Can you change it from an Execute Process task to a Script Task and use C# to compress the file instead?
    – HandyD
    Sep 13, 2018 at 1:28
  • @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.
    – BishNaboB
    Sep 14, 2018 at 10:05

2 Answers 2

Reset to default
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.

Improve this answer
0

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.

Improve this answer
3
  • 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
    Sep 6, 2018 at 18:52
  • I've checked - 64bit mode.
    – BishNaboB
    Sep 7, 2018 at 12:03
  • 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.
    – MattyZDBA
    Sep 10, 2018 at 13:39

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.