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:
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.
[System.AddIn.AddIn("ScriptMain", Version = "1.0", Publisher = "", Description = "")]
public partial class ScriptMain : Microsoft.SqlServer.Dts.Tasks.ScriptTask.VSTARTScriptObjectModelBase
#region VSTA generated code
Success = Microsoft.SqlServer.Dts.Runtime.DTSExecResult.Success,
Failure = Microsoft.SqlServer.Dts.Runtime.DTSExecResult.Failure
/// 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();
//---->>This psline variable will contain your PowerShell script:
string psline = "\"Hello World\"";
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:
Apparently, it is unable to include:
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‘:
Open the ‘Browse‘-tab and select the DLL-file in C:\Temp:
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’:
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 🙂
Enjoy using PowerShell within SSIS!