Execute PowerShell Script from SSIS Package

Problem

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?

Solution

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:

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.

Two package parameters are created for the input variables:

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:

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:

• 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.

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:

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:

To see the actual error message, we need to ignore the 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:

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.

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:

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.

Conclusion

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.

Alternatives

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

• Try it out yourself! You can follow the guidelines in this tip to execute your own PowerShell scripts in SSIS. As an additional exercise, you could try to write the OutPutVariable to a SQL Server table.
• More about Execution Policies: Setting the PowerShell Execution Policy.
• 4 Convenient Ways To Run PowerShell Scripts
• More SSIS tips can be found in this overview.
• More PowerShell tips can be found in this overview.