Run PowerShell scripts in SSIS

This post has been republished via RSS; it originally appeared at: SQL Server Integration Services (SSIS) articles.

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 :

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:

  1. Run gpedit.msc to open the Group Policy Editor;

  2. On the left tree view, navigate to Local Computer Policy\Computer Configuration\Administrative Templates\Windows Components\Windows PowerShell ;

  3. On the right pane, double click on Turn On Script Execution ;

  4. 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.

Leave a Reply

Your email address will not be published. Required fields are marked *


This site uses Akismet to reduce spam. Learn how your comment data is processed.