- Call and execute PowerShell scripts directly from within SQL Server Integration Services.
- Easily pre/post-process messages and tabular data with PowerShell scripting.
Integrate PowerShell Scripting into SQL Server SSIS packages quickly and easily
The PowerShell SSIS Task is the 'swiss army knife' of SQL Server automation. It can be used to create highly-flexible SQL Server workflow solutions via easily configurable PowerShell scripts. When PowerShell SSIS Task is executed through SSIS workflow it executes a pre-configured PowerShell script and passes the input as a parameter. Each instance of the task on a package can define a different PowerShell script to execute.
The PowerShell SSIS Task is perfect for:
- Fast prototyping and POC creation.
- Eliminating redeployment headaches typically involved with changing SSIS Workflows.
- Preprocessing and/or normalizing datasets before passing to other SSIS Tasks
- Rapidly creating custom SSIS Tasks with the full power of PowerShell.
Using the PowerShell SSIS Task
To use the PowerShell SSIS Task, follow these simple steps:
- Open your SSIS package in the Visual Studio designer
- Drag the PowerShell SSIS Task Receive Task task from the Toolbox into your package
- Configure the task properties with the following:
- The PowerShell script to run when the task is executed. You can just write your script directly in the task properties, or dot-source an external script file with your commands.
- Zero or more input variables that you want to be defined in the PowerShell Runspace when the script is executed. You can use this to pass data from SSIS to your PowerShell script.
- The name of a variable to output the data returned by the script. This should be an Object variable that will receive an ADO.NET DataTable containing one row for each PSObject returned by the script, with each column holding the value of a property of said object. If the script returns primitive .NET values, like an int or a string, the __Value__ column will contain its value.
Executing Scripts on Remote Machines
PowerShell SSIS Task also allows you to execute scripts on remote machines using the PowerShell V2 Remoting protocol or the PowerShell Server protocol.
To use PowerShell Remoting, you need to set the following options in the task properties:
- Host: The name or IP address of the remote computer.
- Protocol: set to prRemoting;.
- User and Password: Optional. If not specified, the connection will be attempted using the user the SSIS workflow is running under.
To use PowerShell Server, you need to have PowerShell Server installed and running in the remote machine, and configure the following properties:
- Host: The name or IP address of the remote computer.
- Protocol: set to prSSH.
- SSHAuthMode: Set to amPassword for username/password authentication or to amPublicKey for public key authentication. For the latter, this option needs to have been enabled in the remote PowerShell Server, and the task needs to have access to a certificate the server will accept.
- User: The username to be used for authentication.
- Password: Optional. The host key/certificate used to authenticate the server.
- SSHCert: The certificate to use for public key authentication.
