I have to admit it, I'm a bit of a PowerShell geek. I chat regularly with a colleague of mine James Saull about new ways in which we can use PowerShell. We've already decided we are going to hook up a USB Panic Button to PowerShell so that we can launch our deployment scripts when the button is hit (more on this project as it develops).
One Friday afternoon James mentioned in passing how cool it would be if you could run PowerShell scripts from within a SSIS package. Hmmm, what a great idea. The PowerShell Script Task had been born.
So i set to work creating a control flow task for SSIS that allows you to run PowerShell script. The initial features of the task are:
- SSIS variables can be read in your script:
$file = $ssis.Variables["User::FileName"];
- SSIS variables can be written to in your script:
$ssis.Variables["User::AlbumCount"] = $albumCount;
- You can log information/warning/error messages to SSIS:
$ssis.LogWarning(10, "Something has gone wrong");
- The task implements a custom PowerShell host. This means that host specific cmdlets like Write-Host are integrated with SSIS
There is still more work to be done. In the next release I am planning on:
- Exposing the SSIS connections to PowerShell
- Exposing the current transaction (if applicable) to PowerShell
- Script validation
- Improved script editor
Feel free to download the task and play. Any feedback would be appreciated.
In my next post i will discuss some of the code used in the task specifically around hosting PowerShell.
Enjoy.