First published on MSDN on Jan 26, 2017
PowerShell 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.
Specifically, the PowerShell.exe command-line comes with a -File parameter :
That is, command line like
effectively launches the PowerShell process to execute the script file D:\script.ps1 . 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:
As an illustration of managing Azure resources using Azure PowerShell cmdlets, the following script template can be used to delete files on Azure Storage:
Note that wildcard characters like * can be used in the -Blob parameter to match and delete multiple files at once. For details, please refer to the Remove-AzureStorageBlob cmdlet.
PowerShell 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.
Specifically, the PowerShell.exe command-line comes with a -File parameter :
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. File must be the last parameter in the command, because all characters typed after the File 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 D:\script.ps1 . 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:
- Run gpedit.msc 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 Enabled with an 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:
$context = New-AzureStorageContext -StorageAccountName ... -StorageAccountKey ...
Remove-AzureStorageBlob -Container ... -Blob ... -Context $context
Note that wildcard characters like * can be used in the -Blob parameter to match and delete multiple files at once. For details, please refer to the Remove-AzureStorageBlob cmdlet.
Updated Mar 25, 2019
Version 2.0
