SSIS PowerShell Script Task

Today, I wanted to create a SSIS package which uploads/publishes a CSV-file with deltas to (IBM) MQFT by using PowerShell.
Without going into details about MQFT, I would like to make a note here on how to execute a PowerShell script within an SSIS package.

Note: I’ve chosen to execute the PowerShell within a Script Task, but you’re also able to execute a PowerShell script within an Execute Process Task. More info on that can be found here, here and here.

I found this great blog post which helped me with my first steps:
http://www.maxtblog.com/2012/03/orlando-code-camp-2012-integrating-powershell-into-ssis-script-task-session/

By using a Script Task, you’re able to execute a PowerShell script.
You may want to save the following code as a template:


/*
Microsoft SQL Server Integration Services Script Task
Write scripts using Microsoft Visual C# 2008.
The ScriptMain is the entry point class of the script.
*/

using System;
using System.Data;
using Microsoft.SqlServer.Dts.Runtime;
using System.Windows.Forms;
using System.Management.Automation;
using System.Management.Automation.Runspaces;

namespace ST_e0f329a65fa94200835440a6efd0c0c2.csproj
{
[System.AddIn.AddIn("ScriptMain", Version = "1.0", Publisher = "", Description = "")]
public partial class ScriptMain : Microsoft.SqlServer.Dts.Tasks.ScriptTask.VSTARTScriptObjectModelBase
{

#region VSTA generated code
enum ScriptResults
{
Success = Microsoft.SqlServer.Dts.Runtime.DTSExecResult.Success,
Failure = Microsoft.SqlServer.Dts.Runtime.DTSExecResult.Failure
};
#endregion

///

/// This method is called when this script task executes in the control flow.
/// Before returning from this method, set the value of Dts.TaskResult to indicate success or failure.
/// To open Help, press F1.
///

public void Main()
{
// Code to create a runspace and execute the PowerShell script the pipeline:
Runspace ps = RunspaceFactory.CreateRunspace();
Pipeline pipe = ps.CreatePipeline();
ps.Open();

using (pipe)
{
//---->>This psline variable will contain your PowerShell script:
string psline = "\"Hello World\"";

pipe.Commands.AddScript(psline);
pipe.Invoke();
}
Dts.TaskResult = (int)ScriptResults.Success;
//Dts.TaskResult = (int)ScriptResults.Failure;
}
}
}

The issue I faced was that our server didn’t have the Windows SDK or PowerShell SDK installed. So building the project (C# 2008 in BIDS 2008R2) returned an error:

Visual-Studio-Runspace-error


Apparently, it is unable to include:
using System.Management.Automation
using System.Management.Automation.Runspaces



To resolve this, run the following statement within a PowerShell console to generate the required DLL-file (more info here):
Copy ([PSObject].Assembly.Location) C:\Temp


A DLL-file is generated in C:\Temp which is called ‘System.Management.Automation.dll‘.

We need to refer to this DLL-file within the C#-project (Edit the Script Task within BIDS).
To accomplish this, Right-click on References and select ‘Add Reference‘:

VS-Add-Reference


Open the ‘Browse‘-tab and select the DLL-file in C:\Temp:

Browse-for-the-DLL-file


Last thing, we need to make sure that the DLL-file is copied with the VS project. Right-click on the DLL and open the Properties. Now, select True for ‘Copy Local’:
DLL-properties-Copy-Local


Close the window by clicking OK and (re-)Build the project.


That’s it! Click on ‘Save All‘ and close the Visual Studio environment (script editor) and return to SSIS.
Click OK to close the Script Task and save the SSIS package. You may want to execute the Script Task to verify that everything is working properly 🙂

SSIS-2008R2-ScriptTask-Green

Enjoy using PowerShell within SSIS!

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s