SSIS and PowerShell – Execute process task

Introduction

SQL Server Integration Services (SSIS) and PowerShell (PS) together offer a plethora of opportunities, and some shortcuts when having to import, export, or at times moving data. I have come across packages that contain a Script Task with lines and lines of C# code that, done with PowerShell, could make maintaining that package much easier. Overall, the most common thing I see Script Task doing is accessing the file system or doing some manipulation on a file. One thing I hope that picks up speed in the BI world of SSIS is utilizing PowerShell for these type of actions. This is not to say one is better than the other as you should pick what is best in your eyes, but when I can do operations against the file system with a one-liner in PowerShell it is just easier to maintain that in the package. In this article I will go over how you can use the most common task utilized for executing PowerShell code in an SSIS package: Execute Process Task.

The Setup

Executing PowerShell with the Execute Process Task means you are going to call “powershell.exe” and then pass it some parameters. Now you can just go to a command prompt and type in “powershell /?” to see your options, but at a minimum you will generally use the following:

NoProfile – this ensures it does not try to load any profile on the machine and I always use it as a precautionary measure.
ExecutionPolicy – While PowerShell can be locked down when it comes to the execution policy for calling scripts and commands, it can also be “Bypass”-ed. When you are testing, your policy may allow you to execute PowerShell code. If 6 months from now some domain administrator wants to change that policy for your server, this will ensure they do not break all the packages where you use PowerShell.
Command – This is either a script block (wrap the code in curly braces) or in double-quotes.
File – in place of the command parameter, if you want to call a file.

The version of SSIS tools I will be using: SQL Server Data Tools Business Intelligence for Visual Studio 2013.

Execute Process Task

The execute process task is fairly simple to work with, and only a few fields need to be completed. You can get full documentation on this task with SSIS by going to the link provided in the reference section at the end of this article. You can see the main window of the task properties is the “Process” pane, which I have circled below the main fields we will work with:

Executing Inline Code

When I am dealing with a case where I just need to execute a built-in cmdlet, or the one-liner I need to use is fairly short, I will opt for the “-Command” parameter. Now it is possible to put 100 words into a one-liner, but there is a limit for me as to whether how easily it is to maintain that and making sure it is readable. If we work on the example of testing a file path, the cmdlet I would use is, Test-Path and this is within my limit of just using the command parameter. If there was more business logic to include I would opt for the file instead.

Utilizing variables can vary between individuals and I try not to go to crazy in their use. My thinking in this situation though, I would have a variable to hold my root path, and then a variable that holds my file name that I am working on. I will then have another variable that joins them together via an expression in order to work with the full path to the file, this will make my PowerShell argument easier to read. The final variable I will need is for the arguments I pass to the “powershell.exe” process. So the list of variables and their initial value will look like this, and note when I reference directories or folders I will always include that ending back slash:

FilePath (String) = C:\TEMP\CollegeScorecardRaw_Data\
FileName (String) = MERGED1996_PP.csv
TestFilePath (String) = @[User::FilePath] + @[User::FileName]
Cmd (String) = “-NoProfile -ExecutionPolicy ByPass -Command \”if (Test-Path ‘” + @[User::TestFilePath] + “‘) {exit 0} else {exit 999}\””

I will explain a bit more about the cmd string later.

To pass the variables into the PowerShell command we use the “Cmd” variable, and that is simply configured as an expression for the Arguments property. I prefer doing this, because if I need to update that command I simply update the variable, compared to having to open up the properties window and making the change. You could also configure this variable into a configuration file or project parameter for easier management. I configure the execute process task as shown below in the screenshot:

You will see that the “Arguments” property looks like I manually typed it in, but going to the Expressions pane you can see that I have mapped the property to the expression:

One final thing I will do is simply add two script task that will have a message box defined via C# to visually display whether the test was successful or not. A successful execution where the file does exist, will provide this result:

Where a failure for a file that does not exist will result in this view:

What about that cmd variable?

If you did not know already the execute process task can be finicky at times, so always test. In normal PowerShell scripts if I need to return a true or false around testing a file path I would just execute this:

Test-Path ‘C:\TEMP\MyFile.csv’

This command will return a true or false, however in the execute process task it is expecting an integer value of 0 (zero) for true by default. This cmdlet would not return that by itself so you have to force it to return an integer value, so what if I change it to this:

If (Test-Path ‘C:\TEMP\MyFile.csv’) {0} else {999}

In a PowerShell prompt you would get a zero if that path is good, or 999 if it is not. However again, it does not return that if you execute it calling PowerShell.exe. Why? The reason is that the process itself (PowerShell.exe) is returning a zero for successfully completing the command. So the potential is there for it to overwrite any failure value I need returned. What you do is simply force the exit value, if you are familiar with the batch file days you probably remember doing this all the time. So the end result of the command to get the expected result in SSIS:

If (Test-Path ‘C:\TEMP\MyFile.csv’) {exit 0} else {exit 999}

Wrap Up

PowerShell has a huge potential to save you having to write any raw C# code for your packages. In the sense that it is built upon .NET framework itself, a lot of that code is accessible in much easier fashion. Just remember to have it in the back of your mind when designing your packages and you find yourself going to C#, “how would I do this in PowerShell?”

The solution used to write the article is available here

Shawn Melton

Shawn Melton is a SQL Server consultant at Pythian, a global IT services company based out of Ottawa - Canada.

After spending 6 years in the system and network administration world he found he enjoyed working and learning SQL Server. Since 2010 he has been involved in SQL Server.

He is passionate about PowerShell and automation around SQL Server and Windows. His experience is focused in SQL Server administration and some BI development (SSIS, SSRS).

View all posts by Shawn Melton