How to Execute a PowerShell Script from SSIS

Question

Does anyone know how to execute a PowerShell script from SSIS? I have created the script and it works from from the command line. The script takes a couple of command line parameters, which work fine when called from cmd.exe.

I'm using an Execute Process Task in SSIS and cannot get the script file to execute. I'm using expressions to pass in the name of the script and the command line arguments. The task is returning an incomplete string token error.

SQL Server Integration Services. A Microsoft Tool/Platform for moving data in and out of SQL Server. — Ed Mays
– Ed Mays, Commented Mar 3, 2009 at 5:16
What command line are you processing to the execute process task? — Richard
– Richard, Commented Mar 3, 2009 at 14:21

Richard · Accepted Answer · 2009-03-03 14:22:56Z

5

From VS to launch PSH with an extra script (for a Cmdlet project) I use the following commandline:

powershell -noexit -command ". ./Startup.ps1"

The -noexit will keep the instance around (so you wouldn't want that), putting all the real commands in a script to be dot-sourced avoids a really long command line.

answered Mar 3, 2009 at 14:22

Richard

110k21 gold badges214 silver badges279 bronze badges

Sign up to request clarification or add additional context in comments.

5 Comments

Ed Mays Over a year ago

How would you handle command line arguments? The script expects 2 arguments.

Richard Over a year ago

Put them in the script you dot source, this can of course call another script.

Ed Mays Over a year ago

I finally figured it out. The SSIS task wants the parameters without any formatting. I was quoting the parameters and it didn't like that. Also, no embedded spaces in the arguments.

dance2die Over a year ago

@Ed Mays: Would you update the question on how you were able to pass parameters? Thanks.

Thronk Over a year ago

This answer does nothing to explain how the parameters are passed from ssis. putting them in the script isn't optimal if the parameters originate in SSIS process

Dmytro Sly · Accepted Answer · 2020-06-25 10:06:56Z

2

Go to Execute Process Task Editor -> Process tab and set the following options:

Executable - C:\Windows\System32\WindowsPowerShell\v1.0\powershell.exe
Arguments - -File ".\PowershellScript/ps1" "arg_1_value" "arg_2_value" ... "arg_n_value"

Refer to the below screenshot:

answered Jun 25, 2020 at 10:06

Dmytro Sly

3134 silver badges13 bronze badges

Collectives™ on Stack Overflow

How to Execute a PowerShell Script from SSIS

2 Answers 2

5 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

5 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related