SSIS and Powershell are two of my current loves in technology, so of course, I was excited to see someone has made a Powershell Script Task. I’ve been meaning to try this out for months. Unfortunately it looks like the project isn’t currently active, but I thought I’d still give it a whirl, and post it here hoping to save others a little time on setup.

First you’ll need to download the source code as there’s no installation package. Then you’ll need to compile the project to produce the dll. I used Visual Studio 2008 but I guess Visual C# Express Edition will do it.

Double click the file called Defiant.SqlServer.PowerShellScriptTask.csproj from the source code you downloaded. This will open the project in your IDE. Build the project and a bin\debug  directory containing a dll will be created in the project folder.

We are only interested in the file called Defiant.SqServer.PowerShellScriptTask.dll. We will need to register this file in the GAC and copy it to a place where SSIS can find it. Save the commands below, with appropriate modifications, to a batch file.

cd\
c:
cd C:\Program Files\Microsoft SDKs\Windows\v6.0A\bin
gacutil /uf "Defiant.SqlServer.PowerShellScriptTask";
gacutil /if "C:\Users\Rhys\Desktop\Source\Defaint.SqlServer.PowerShellScriptTask\bin\Debug\Defiant.SqlServer.PowerShellScriptTask.dll";
copy "C:\Users\Rhys\Desktop\Source\Defaint.SqlServer.PowerShellScriptTask\bin\Debug\Defiant.SqlServer.PowerShellScriptTask.dll"; "C:\Program Files\Microsoft SQL Server\100\DTS\Tasks";

You may need to modify; the path on line 3, this is the location of gacutil. line5, this is the full path of Defiant.SqlServer.PowerShellScriptTask.dll. line 6 the full path of Defiant.SqlServer.PowerShellScriptTask.dll and the path to DTS\Tasks for the instance on SQL Server you want to copy it to. For some reason I couldn’t get this to work on 2005 but it worked fine for 2008. Restart the SSIS service using services.msc then we are ready to fire up BIDS.

When inside BIDS the Powershell Script Task will not appear in the toolbox automatically. Right click on the toolbox and select Choose Items. In the SSIS Control Flow Items tab scroll down to Powershell Script Task and check the box next to it.

Now the task will appear in the toolbox. Drop the task onto the designer to begin working with it.

Right click the task and choose edit. Anyone who has worked with the VB.Net \ C# Script Task should work this out straight away.

Let’s try something simple first. Enter the below line of Powershell in the Script window;

Write-Host "This is a test!";

Click OK and then run the package. Below is the output I received.

SSIS package "Package.dtsx" starting.
Information: 0x0 at PowerShell Script Task, PowerShellScriptTask: This is a test!
Information: 0x0 at PowerShell Script Task, PowerShellScriptTask:
SSIS package "Package.dtsx" finished: Success.

Great, it works! Now lets try working with some variables. Add two variables like below (substituting your name for mine);

Then edit the Powershell Script Task and add these variables as below.

Add the below Powershell to the Script window;

$ssis.Variables["User::write"] = $ssis.Variables["User::readOnly"];

This code just assigns the value of the readOnly variable to write. Next drop a VB.net Script task onto the designer and connect the Powershell Script Task to it. We’re going to add code here to display a message box with the contents of the write variable. This will show that our Powershell script task has successfully read from, and written to, our SSIS variables.

Click edit script and and the below VB.Net code.

Imports System
Imports System.Data
Imports System.Math
Imports Microsoft.SqlServer.Dts.Runtime

 _
 _
Partial Public Class ScriptMain
	Inherits Microsoft.SqlServer.Dts.Tasks.ScriptTask.VSTARTScriptObjectModelBase

	Enum ScriptResults
		Success = Microsoft.SqlServer.Dts.Runtime.DTSExecResult.Success
		Failure = Microsoft.SqlServer.Dts.Runtime.DTSExecResult.Failure
	End Enum

	Public Sub Main()
        Dts.Variables("write").Value = Dts.Variables("readOnly").Value.ToString
        MsgBox("Hello " & Dts.Variables("write").Value.ToString)
        Dts.TaskResult = ScriptResults.Success
    End Sub

End Class

Your final package should look something like below.

Execute the package and, if all goes well, you should see the below message box.

So that’s the basics of working with the Powershell Script Task. I’ve got over my deep hatred of VB.net, since working with it in SSIS so much, but Powershell is a welcome addition to my box of tricks! I’m looking forward to deploying this in future projects. It’s a shame the project seems inactive but this is really something I’d like to see Microsoft provide out-of-the-box in SSIS.