The script has a parameter to supply a csv filepath. It checks whether the folder mentioned in this path exists. If not it creates it. Then it exports the data to specified CSV file.

A) Using a Custom Task
You could look for a custom task like the one on codeplex (also see this example), but it seems not to be an active project. And so far I haven't found any commercial PowerShell Tasks for SSIS.

B) Using the Execute Process Task
To execute the PowerShell script with an Execute Process Task you need a command that looks like:
powershell.exe -command "c:\temp\hotfixes.ps1 -FilePathCsv c:\temp\export\hotfixes.csv"

Add an Execute Process Task to the Control Flow and give it a suitable name like "EPR - Create hotfixes CSV". Edit it and go to the Process page. In the Excutable property you add the complete path of the PowerShell executable. For example: C:\Windows\System32\WindowsPowerShell\v1.0\powershell.exeAnd in the Arguments property you add the rest of the command: -command "c:\temp\hotfixes.ps1 -FilePathCsv c:\temp\export\hotfixes.csv"

PowerShell via Execute Process Task

This step is optional but with two SSIS string variables (or parameters) containing the paths and an expression on the arguments property you can make it a little more flexible: "-command \"" + @[User::PathPowerShellScript] + " -FilePathCsv " + @[User::PathHotfixes] + "\""

Process Task with variables(expressions) in the arguments

When you run this in Visual Studio you will see a PowerShell window for a couple of seconds. With the StandardOutputVariable and StandardErrorVariable you can catch the output of the PowerShell script into an SSIS variable.

PowerShell window when executing the Execute Process Task

C) Using the Script TaskIf you don't like the Execute Process Task then you could use the Script Task to execute a PowerShell script, but to use PowerShell in .NET you need to install the Windows Software Development Kit (SDK) for Windows. It contains the assembly System.Management.Automation.dll which we need to reference in our Script Task later on. Don't forget to install it on all your machines (DTAP: Development, Test, Acceptance and Production).

Choose Windows Software Development Kit for the libraries

First add two string variables (or parameters) to your package. PathHotfixes containts the filepath of the to be created CSV file and PathPowerShellScript contains the filepath of the PowerShell file.

String variables

Add the Script Task to the Control Flow and give it a suitable name like "SCR - Create hotfixes CSV". Then edit it choose the Script Language (C# or VB.NET).

Choose ScriptLanguage C# or VB

Then add the two string variables (or parameters) as Read Only Variables. We are using them to avoid hardcoded paths in our script.

ReadOnlyVariables

After that hit the Edit Script button to open the VSTA environment. In the Solution Explorer (upper right corner) right click the References and choose Add Reference... then browse to C:\Program Files (x86)\Reference Assemblies\Microsoft\WindowsPowerShell\3.0 and select the System.Management.Automation.dll file.

Add Reference

In the code we need to add extra namespaces (usings in C# and imports VB) to shorten the code. You can either add these rows to the existing namespaces by adding them in the region Namespaces. Or you can add a separate region for custom namespaces.

' VB.NET code
' This method executes a PowerShell file with a parameter
Public Sub Main()
' Create a new Runspace to run your command in
Dim runspaceConfiguration As RunspaceConfiguration = RunspaceConfiguration.Create()
Dim runspace As Runspace = RunspaceFactory.CreateRunspace(runspaceConfiguration)
runspace.Open()
' Create a new command with a parameter
Dim command As New Command(Dts.Variables("User::PathPowerShellScript").Value.ToString())
Dim commandParameter As New CommandParameter("FilePathCsv", Dts.Variables("User::PathHotfixes").Value.ToString())
command.Parameters.Add(commandParameter)
' Execute the PowerShell script in the Runspace
Dim pipeline As Pipeline = runspace.CreatePipeline()
pipeline.Commands.Add(command)
pipeline.Invoke()
' Close the Script Task and return Success
Dts.TaskResult = ScriptResults.Success
End Sub

Alternative: If you don't want to store the PowerShell code in a separate file because it makes it harder to deploy it through the DTAP environment then you can also add the PowerShell code directly in your .NET code or store it in an SSIS variable and pass that to the Script Task. In that case the code slightly changes.

After that close the VSTA editor and the Script Task Editor and test the Script Task. If it was successful you can use a Data Flow Task to read the CSV file. With this relative easy code and this custom task example you could create your own custom PowerShell task.

Sunday, 6 December 2015

Case
I tried disabling the TFS multiple checkout for an SSIS project, but my collegue and me can still check out the same SSIS package at the same time causing all kinds of issues when checking in the changed package. How can I disable multiple checkout?

Disable multiple checkout in TFS

Solution
You need to check your workspaces. If one of you is still using a local workspace then you can still checkout the same package multiple times.

Go to the Source Control Explorer in Visual Studio. You can find the link in the Team Explorer pane. Then (1) open the Workspace selectbox and choose Workspaces... In the Manage Workspaces window click (2) on the Edit... button and then (3) on the Advanced >> button.

Edit Workspace

In the advanced option change the location from Local to Server and click on OK and one more time in the next window.

Set workspace location to server

If you both use the workspace on the server then you will get an message that the SSIS package is checked out by user xxx.

Package checked out by colleague Menno

Now you only need to remember to don't keep the project or solution checked out otherwise no one else can add (, delete or rename) projects/packages/connection managers/etc. When adding a new package the project will be checked out. To prevent long check outs, you should first give the new/empty package its correct name and then check-in everything (the project and the new/empty package). Only then you can start building the new package!Also see MSND for more information about local and server workspaces.