First published on MSDN on Jan 26, 2017
is a powerful task automation tool from Microsoft. With
Azure PowerShell cmdlets
, you can write PowerShell scripts to automate various Azure-related tasks. It would be quite nice if we can run PowerShell scripts within SSIS. Although SSIS does not provide something like Execute PowerShell Script Task out of the box, it does have an Execute Process Task which can be used to run PowerShell scripts just as easily.
Runs the specified script in the local scope ("dot-sourced"), so that the functions and variables that the script creates are available in the current session. Enter the script file path and any parameters.
must be the last parameter in the command, because all characters typed after the
parameter name are interpreted as the script file path followed by the script parameters and their values
That is, command line like
PowerShell.exe -File D:\script.ps1
effectively launches the PowerShell process to execute the script file
. This command line operation amounts to the following Execute Process Task configuration:
As simple as that.
Please note that you may need to enable PowerShell script execution on the machine in order for this to work, which can be done using steps similar to the following:
to open the Group Policy Editor;
On the left tree view, navigate to
Local Computer Policy\Computer Configuration\Administrative Templates\Windows Components\Windows PowerShell
On the right pane, double click on
Turn On Script Execution
On the editor that pops up, choose
allow all scripts
execution policy option.
As an illustration of managing Azure resources using Azure PowerShell cmdlets, the following script template can be used to delete files on Azure Storage: