How to Execute a PowerShell Script from an SSIS Package


By:   |   Updated: 2020-12-07   |   Comments   |   Related: More > Integration Services Development


A Powerful and Secure Alternative to SSIS

Free MSSQLTips Webinar: A Powerful and Secure Alternative to SSIS

SSIS is the de facto solution for SQL Server data integration and transformation. However, standard ETL processes are typically slow and result in data that is not always current which impedes decision making and business processes. Organizations are increasingly in need of better performing real-time data access without sacrificing critical access controls and data activity monitoring.


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:

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.

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


Last Updated: 2020-12-07


get scripts

next tip button



About the author
MSSQLTips author Koen Verbeeck Koen Verbeeck is a BI professional, specializing in the Microsoft BI stack with a particular love for SSIS.

View all my tips



Comments For This Article





download





Recommended Reading

Import UTF-8 Unicode Special Characters with SQL Server Integration Services

SSIS Expression Examples for Dates, String Concatenation, Dynamic File Names and More

Import Text and CSV Files into SQL Server Database with SSIS Script Task

Extract, Import and Migrate SSIS Project

Install SQL Server Integration Services in Visual Studio 2019














get free sql tips
agree to terms