Overview
PowerShell Task is an SSIS Control Flow Task that allows execution of any custom logic via PowerShell. Use the PowerShell Task to run PowerShell scripts directly in your SSIS package.
The PowerShell Task supports the following features:
- Convenient script editing within an embedded PowerShell editor
- Access to SSIS variables and parameters from within PowerShell scripts
- Access to the results of PowerShell script execution.
- Access to the PowerShell output streams and ability to forward them to the SSIS log and/or save them in SSIS variables (for custom processing)
- Referencing a script defined in another PowerShell task and calling it with custom parameters
Editor
The Editor Dialog contains the following tabs:
Referencing code from another PowerShell Task
To avoid code duplication, the code of a PowerShell Task can be stored in another PowerShell Task. The properties under the Linked Task category allow referencing the source PowerShell Task:
- PackageConnection - allows choosing the package, where the source PowerShell Task is (i.e. the current package or an external one).
- PowerShellTask - allows selecting the source PowerShell Task in the selected package.
If you have specified a source PowerShell Task, you can unlink it later by clicking the button Remove link below.
Passing parameters to the script
To execute parametrized scripts the property under the Input category should be used:
- ParametersString - parameters in PowerShell format. Can be named parameters (e.g.
-Param1 123 -Param2 Report
) or positional parameters (e.g.123, Report
).
Logging or collecting output streams
The properties under the Output Streams category are used to configure logging or writing an output stream to a variable. By default the task is configured to log the Error and Warning streams.
- Process - defines which stream will be processed, can be selected more than one stream. All PowerShell streams are available for selection - Error, Warning, Progress, Verbose, Debug.
When a stream is selected to be processed a corresponding property to define how the stream will be processed is visualized. Available modes are Log and Variable - logging to a SSIS Log or saving to a variable. The properties are:
- Error
- Warning
- Progress
- Verbose
- Debug
When processing mode Variable is selected a corresponding property to select variable is visualized. Variable of object type must be used. The properties are:
- ErrorVariable
- WarningVariable
- ProgressVariable
- VerboseVariable
- DebugVariable
Configuring the Task ExecutionValue
In PowerShell, each statement having a result is returned, even without an explicit return
keyword.
This script will return the list { 1, "text" }:
$var1 = 1 $var2 = "text" $var1 $var2
The task ExecutionValue property will contain the complete result of the script execution or a relevant subset. The subset can be configured in category Result using the property ExecutionValueType, which offers the following options:
- Full Result - all returned values are stored. This is the default value of the property.
- Last Sequence - last returned values of the same type are stored (useful when the last script block returns some collection of values).
- Last Item - only the last item is stored.
Script tab
The tab displays the PowerShell script code and allows editing it. The embedded editor is the one that comes with the particular version of Visual Studio.
At the bottom of the dialog, there are two buttons that make it convenient to load the PowerShell code from a file and save the code to a file.
Referencing SSIS variables and parameters
SSIS variables and package/project parameters are accessible during execution via a special variable, called $SSIS_VARS
. All variables or parameters with package or task scope are available. Package & project parameters are read-only.
Examples
Reading from a variable:
$userName = $SSIS_VARS.Item('System::UserName').Value
Writing to a variable:
$SSIS_VARS.Item('User::MyVariable').Value = 'some string'
Reading from a package parameter:
$kind = $SSIS_VARS.Item('$Package::ReportKind').Value
Reading from a sensitive project parameter:
$password = $SSIS_VARS.Item('$Project::SitePassword').SensitiveValue
Locking and unlocking
Explicit locking of a variable is not required. Into the above examples, variables are locked and unlocked automatically. To speed up script execution an explicit locking can be used.
Shared locking (ReadOnly Variable):
$userNameVariable = $SSIS_VARS.Item('System::UserName') $userNameVariable.LockShared() $userName = $userNameVariable.Value $userNameVariable.Unlock()
Exclusive locking (ReadWrite Variable):
$myVariable = $SSIS_VARS.Item('User::MyVariable') $myVariable.LockExclusive() $myVariable.Value = 'a string value' $myVariable.Unlock()
Unlocking is not required. All locked variables will be unlocked after the script execution. An exclusively locked variable must be unlocked to be available to another Task running in parallel.
Low level access
Various properties/methods for access to the underlying Microsoft.SqlServer.Dts.Runtime.Variable
object are available like readOnly
, description
, etc.
Quick Start
We will setup a PowerShell Task to automate database operations - create test database and table, insert data. Additionally we will log the progress and capture the results in a variable.
To run the example script the free PowerShell module dbatools needs to be installed. Instructions how to install are here.
Configure variable to store script result
PowerShell returns the results of each statement. In our script only the last statement returns value. We will save the statement results in a variable by using the ExecValueVariable property. The task is configured to return all results in a List
. dbatools query command returns DataRow
objects.
Knowledge Base
- Where can I find the documentation for the PowerShell Task?
- Error Message: Could not load file or assembly System.IO.Compression
What's New
- Fixed: Scripts using
Import-Module
command failed (Thank you, Mark).
- New: Introduced task.
Related documentation
COZYROC SSIS+ Components Suite is free for testing in your development environment.
A licensed version can be deployed on-premises, on Azure-SSIS IR and on COZYROC Cloud.