Powershell in SSIS

Question

What would I need to do in order to run a PowerShell script in an SSIS package?

Does PowerShell need to be installed on the SQL server? How do I actually call the script in SSIS?

Thanks

Answer 1

Yes, PowerShell needs to be installed. Then, you can use an Execute Process Task to point to the PowerShell executable and give the script as an argument.

Like so:

Executable: %windir%\system32\WindowsPowerShell\v1.0\powershell.exe
Arguments:  C:\path\to\script.ps1

Answer 2

How would we get the $LastExitCode of the powersript and handle it in Execute Process Task to know if the process succeed or not?

Thanks.