How to Execute a PowerShell Script from an SSIS Package

By:   |   Comments   |   Related: More > Integration Services Development


I have a script written in PowerShell, which needs to be executed as part of a larger ETL process. We use Integration Services to implement this process. However, I cannot seem to find a task which can execute PowerShell scripts? How can this be done in an Integration Services package?


Integration Services (SSIS) is a mature ETL tool part of the Microsoft Data Platform. It supports a variety of sources and can perform many tasks. However, there's no native support to directly execute a PowerShell script, such as we do have for example for executing SQL scripts. Luckily, there's an easy work around: the Execute Process Task. This task can execute any application or batch file. As such, it can also call the PowerShell.exe executable to execute a PowerShell script.

Executing a PowerShell Script from the Execute Process Task

Sample Script

In this tip we'll use a short PowerShell script to illustrate the implementation in SSIS. There's no need for a complex script, the focus is on the integration in the SSIS package. This sample script takes two integer input parameters, multiplies them and returns the result:

#declare calling params 
param( [int] $input1, [int] $input2 ) 
#multiple inputs
$result = $input1 * $input2
#write output
Write-Host $result

An example of an execution of the script:

executing PoSh script from the command line

With this script, we can test how we can pass input parameters to the script, but also if we can capture the output.

Sample Package

The SSIS package is straight forward: one Execute Process Task to execute the PowerShell script and one Execute SQL Task. This last task serves only as a dummy and is used to create a precedence constraint after the Execute Process Task.

sample ssis package

Two package parameters are created for the input variables:

input parameters

If you're working with the SSIS package deployment model (where parameters are not available), these can be replaced with normal SSIS variables. For the output, a variable is created:

output variable

Its default value is set to 0, so we can easily check if the value was overwritten by the Execute Process Task. The Execute Process task needs to be configured in the following manner:

config of the Execute Process Task
  • Executable: The full path to the application you want to execute. In our case, powershell.exe. On a Windows machine, this should be: C:\Windows\System32\WindowsPowerShell\v1.0\powershell.exe. Don't be misled by the v1.0 in the path name, it doesn't necessarily indicate the actual PowerShell version.
  • Arguments: The command line arguments for the executable specified the line above. For PowerShell, we need to specify the ExecutionPolicy and the command. The command contains the location of the PowerShell script and the two input variables.
  • StandardOutputVariable: the output variable to which we want to write the result of the script.

In the example above, the two input variables are hard-coded into the command. To pass the values of the two parameters, we'll need to use an expression. Let's create another variable of the string data type.

variable to hold Posh command

The following expression is used:

"-ExecutionPolicy Unrestricted -command \"C:\\poshscript\\Test.ps1 " + (DT_WSTR, 10) @[$Package::InputParameter1]  + " " +  (DT_WSTR, 10) @[$Package::InputParameter2] + "\""

Keep in mind the command has backslashes (in the file name) and double quotes. Both need to be escaped with a backslash character. Also, the two input parameters need to be converted to string data types (DT_WSTR in the SSIS expression language) so they can be concatenated with the rest of the command. In the Execute Process Task, we need to put an expression on the Arguments property:

set expression on property

You might've noticed the ExecutionPolicy switch is set to Unrestricted in the arguments. Execution Policies are a safety feature of PowerShell and they determine how exactly a script can be executed. More information can be found in the documentation. The default policy on Windows machines is Restricted, which allows commands to be executed but not script tasks. If the Execution Policy is not changed, the Execute Process Task will fail. However, the error message will just complain the process exit code was not expected:

error message process code

To see the actual error message, we need to ignore the process exit code:

ignore process exit code

We also need to change the data type of OutputVariable to string, because the error message will be written to this variable. When debugging (put a breakpoint on the Execute SQL Task by selecting it and hitting F9), we can see the error message in the value of the variable:

error message execution policy

The error message states the script is not digitally signed and therefore cannot be loaded. To circumvent this, the Execution Policy is set to Unrestricted.

Everything seems to be ready to execute the PowerShell script. However, when executing the package, the Execute Process Task fails with an error message saying the type of the OutputVariable is not correct.

error message data type

PowerShell tries to assign a string value to the variable, but since it has an integer data type this fails. This is odd, since the output of the script is supposed to be an integer. When we change the data type to string and run the package, we can inspect the value of the variable in the locals window:

issue with return value

Apparently, PowerShell added the \n character (which stands for newline) to the integer value 30. The script works, but it returns a value which cannot be stored in an integer data type. If you wish to use the variable at another location, you might want to clean it up first.


We can execute a PowerShell script in an SSIS package using an Execute Process Task. Using an expression, we can pass input variables to the script. Optional output can be captured in an SSIS variable. There are two caveats:

  1. Make sure the correct Execution Policy is set so the script can be executed.
  2. Take care choosing data types as there might be a conflict.


If the ETL process is scheduled through SQL Server Agent, you can also use the PowerShell job step to execute a PowerShell script. An example can be found in the tip Using a PowerShell Script to delete old files for SQL Server. If you want to keep it in an SSIS package, you could also use a .NET script task to launch the PowerShell script. Although, if you're already using .NET, why use PowerShell at all? An example is given in this blog post.

Next Steps

sql server categories

sql server webinars

subscribe to mssqltips

sql server tutorials

sql server white papers

next tip

About the author
MSSQLTips author Koen Verbeeck Koen Verbeeck is a seasoned business intelligence consultant at AE. He has over a decade of experience with the Microsoft Data Platform in numerous industries. He holds several certifications and is a prolific writer contributing content about SSIS, ADF, SSAS, SSRS, MDS, Power BI, Snowflake and Azure services. He has spoken at PASS, SQLBits, dataMinds Connect and delivers webinars on Koen has been awarded the Microsoft MVP data platform award for many years.

This author pledges the content of this article is based on professional experience and not AI generated.

View all my tips

Comments For This Article

get free sql tips
agree to terms