Powershell Script Task for SSIS

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. bin debug 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. choose toolbox items Now the task will appear in the toolbox. Drop the task onto the designer to begin working with it. Powershell Script Task Right click the task and choose edit. Anyone who has worked with the VB.Net \ C# Script Task should work this out straight away. Powershell Script Task Editor Let’s try something simple first. Enter the below line of Powershell in the Script window;

?View Code POWERSHELL
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); ssis variable powershell Then edit the Powershell Script Task and add these variables as below. ssis powershell script task variables Add the below Powershell to the Script window;

?View Code POWERSHELL
$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. script task edit Click edit script and add 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()
        MsgBox("Hello " & Dts.Variables("write").Value.ToString)
        Dts.TaskResult = ScriptResults.Success
    End Sub

End Class

UPDATE: 2015-06-09. Thanks to Michelle who spotted my stupid line of code here, removed… Dts.Variables(“write”).Value = Dts.Variables(“readOnly”).Value.ToString

Your final package should look something like below. powershell_final_ssis_package Execute the package and, if all goes well, you should see the below message box. MsgBox_Powershell_Script_Task 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.


15 Comments

  1. Rhys,

    If you’ve compiled it for 2008, you should upload the DLL back to the CodePlex project as an attachment on a Discussion post or something. Though there’s no installer even if you do that, it takes it one step closer to making it easier for someone else to use it.

    (I need to start learning Powershell!)

  2. Rhys says:

    Hi Todd,

    I didn’t have the ability to attach files to a discussion on codeplex. So I’ve uploaded it here…

    http://www.youdidwhatwithtsql.com/wp-content/downloads/ssis_tasks/Defiant.SqlServer.PowerShellScriptTask.dll

    Rhys

  3. […] Powershell Script Task for SSIS – Good post on a Powershell Script Task. […]

  4. Robin Tanner says:

    Rhys,

    In the below code, I’m trying to pipe a list of filenames to an object variable.

    cls
    # PowerShell script to list the txt files under the IDX folder
    cd\
    cd \\[FileServer]\[Directory]
    $Dir = get-childitem \\[FileServer]\[Directory] -recurse
    $List = $Dir | where {$_.extension -eq “.txt”}
    $FileNames = $List | ft name
    $ssis.Variables[“User::FileNames”] = $FileNames;

    However, when I do this, it tells me the following:

    Exception setting “CursorPosition”: “The method or operation is not implemented.”

    Any ideas..?

  5. Rhys says:

    Hi Robin,

    I’ve not had the chance to run your code but try removing the ‘cls’. I think this may be causing your error.

    I see your using the -recurse option so using the ForEach loop to get filenames isn’t an option for you?

    Rhys

  6. Steph Sullivan says:

    Hiya,
    Thanks for the excellent task available for free.

    I’m having difficulty inputting multiple variables into the task. Could you explain the syntax please?

    Thanks in advance,
    Steph

  7. Rhys says:

    Hi Steph,

    I just compiled this I didn’t write it.

    To use multiple variables just enter them as a comma-separated list in the Read-only/write variables text boxes. ie. variable1,variable2,variable3…

    You can then access then with the following Powershell code…

    ?View Code POWERSHELL
    $var = $ssis.Variables["User::variable_name"];

    Hope that helps.

    Cheers,

    Rhys

  8. Steph Sullivan says:

    I must be doing something wrong because I get this error message:
    Error: Failed to lock variable “FolderNew, FolderRoot, PSDevServer, PSLiveServer” for read access with error 0xC0010001 “The variable cannot be found. This occurs when an attempt is made to retrieve a variable from the Variables collection on a container during execution of the package, and the variable is not there. The variable name may have changed or the variable is not being created.”.

    In the read-only section I’m putting: “PSLiveServer, PSDevServer, FolderRoot, FolderNew” (without the speech marks)

    It works on just one variable, but then of course it fails because I haven’t passed in enough variables for the powershell task.

  9. Rhys says:

    Hi Steph,

    Mmmmm, all sounds ok. Perhaps try removing the spaces in your list?

    I’m not on a computer with BIDS but post your Powershell code and I’ll have a look into it tomorrow night.

    Cheers,

    Rhys

  10. Steph Sullivan says:

    I checked without spaces but it still didn’t work. Thanks for looking at this for me.

    #variable writing
    $fileRef=$ssis.Variables[“User::PSLiveServer”]
    $fileDif=$ssis.Variables[“User::PSDevServer”]
    $dest=$ssis.Variables[“User::FolderRoot”]
    $compFolder=$ssis.Variables[“User::FolderNew”]
    $compFile=”PHPScripts.csv”
    $fileArray=get-childitem $fileRef -include “*.php” -name
    $fileClearOut=get-childitem $dest -include “*.csv” -name

    #delete any existing csvs created in last execution
    get-childitem $dest -include “*.csv” |remove-item

    #creates file results to be output to
    if ((Test-Path -path $compFolder) -ne $True)
    {
    new-item $compFolder -type folder -force
    }
    if ((Test-Path -path ($compFolder + $compFile)) -ne $True)
    {
    new-item ($compFolder + $compFile) -type file -force
    }

    #identifies changes and creates a csv for each changed file
    foreach ($element in $fileArray)
    {
    #has to create a dummy file for files that don’t exist in diff directory
    if ((Test-Path -path ($fileDif + $element)) -ne $True)
    {
    new-item ($fileDif + $element) -type file -force
    }

    $strRef = get-content -path ($fileRef + $element)
    $strDif = get-content -path ($fileDif + $element)
    $output = $dest + $element.replace(“.php”,”.csv”)

    compare-object $strRef $strDif | new-item $output -type file -force
    }

    #delete dummy files
    get-childitem $fileDif -include “*.php” | ? {$_.LastWriteTime -gt (get-date).AddMinutes(-5)} | del

    #gather script names
    get-childitem $dest -include “*.csv” -name | out-file -FilePath ($compFolder + $compFile) -force

  11. Bert says:

    Use semi-colon (;) as a variable separator (no spaces).

  12. Michele says:

    Hi-

    Your VB code assigns the variable,

    Dts.Variables(“write”).Value = Dts.Variables(“readOnly”).Value.ToString

    so how can you tell that the Powershell script is doing anything? I removed the above code, and it does not work for me.

    Am I missing something?

    -Michele

  13. Rhys says:

    Hi Michele,

    I haven’t worked with SSIS for sometime now I’m afraid so I’m not sure how useful I can be.

    Yes. That section of code shouldn’t be there. I’ll remove it.

    What error do you get? I would probably have to refresh my memory about SSIS but are you passing in the variable to the script task? I did other stuff with this so I know the Powershell task worked. Note the output when I do “Write-Host”.

    Rhys

  14. Michele says:

    Hi Rhys,

    Thanks for replying 5.5 years after posting this! I seriously didn’t think I’d ever hear anything. I’m still stuck with SQL 2008 R2/VS 2008 for SSIS, so this is helpful for me. I downloaded and installed the dll (did not attempt to compile myself).

    The first PS script successfully displayed the Write-Host to the output window.

    Then I added the “write” variable and assign it to the “ReadOnly” variable in the PS script. After assigning the value, I Write-Host in PS and can see that the assignment took place successfully in PS.

    One difference is that I set a default value for your “write” variable in SSIS so I could see if that’s what it would display.

    When I display the variable in C# that was supposed to have been assigned by PS, it shows the default value and not the “ReadOnly” variable value (that was assigned to “write” by PS). So, unfortunately, the PS Task write variable does not seem to be getting updated and passed to the C# task. Scope for all SSIS variables is the Package.

    So, if you have any ideas, LMK. Thx,
    -Michele

  15. Rhys says:

    Hello Michele,

    Well, as you noted I wrote this a long time ago. It would have been on 2008 I think, perhaps even 2005, so the SSIS runtime could have changed. This is a wild stab in the dark but… perhaps the variables aren’t accessed through $ssis anymore. Maybe trying downloading and compiling the code yourself. There’s an update from at least 2013 according to the site…

    http://powershellscripttask.codeplex.com/SourceControl/latest

    Good luck!

    Cheers,

    Rhys

Leave a Reply