How To - Tasks and Transformations: Execute Process

For the past three weeks, I’ve been demonstrating how to construct and use SSIS workflow related tasks. Continuing with that theme, I’m going to walk through the Execute Process task.

Background:

The Execute Process task provides flexibility in your package workflow, by allowing you to run a Windows application or batch script. There are tons of scenarios where this may be useful. Common ones include compressing or expanding a file before using it further in your workflow; for instance, you may have a file generated by the WMI Data Reader task that you want to compress before sending it to someone via the SendMail task. Alternatively, if you need custom information that can’t be obtained via an existing SSIS task, you could use Execute Process to run an application that generates the data you need, and then use that data in your workflow.

For this post’s example, I’m going to implement an Execute Process task in BIDS that executes a sample application. While this is very similar to executing an application that will zip or unzip a file, it gives me an opportunity to point out differences.

Using BIDS:

As always, the first step is to find the task in the Toolbox.

Then, drag and drop the task onto the package’s design surface.

The red X icon the task indicates the task has errors.

Double clicking on the Execute Process task brings up its task editor dialog. Clicking on the Process menu item in the left ListBox will let you see the Execute Process task specific properties.

The two key properties in this task are Executable and Arguments; both are located near the top of the dialog. Both properties relate to running a command in a command prompt. For instance, if you want to extract a file from a zip file, you could:

Open a command prompt Use the cd command to change the directory to C:Program Fileswinzip Then run wzunzip.exe /e C:docsfileToUnzip.zip C:unzippedFilesDir where C:docsfileToUnzip.zip is the zip file and C:unzippedFilesDir is where the extracted file should be placed. To emulate this with the Execute Process task, you would set the Executable property to C:Program Fileswinzipwzunzip.exe and set the Arguments property to /e C:docsfileToUnzip.zip C:unzippedFilesDir. Notice that the Executable property takes an absolute path to the executable.

For my sample, I have a small Visual Basic application that I am going to run with the Execute Process task. Of course, you can run any Windows application using this task.

To set the Executable property, click inside the Executable property’s text field. One option here is to simply enter the full path to your executable.

If you prefer a graphical approach, or need to search for the executable, click on the ellipses button to display a dialog box that will let you select an executable.

Related to the Executable property are the RequireFullFileName and WorkingDirectory properties. RequireFullFileName lets you fail the task if the executable isn’t found at its specified location. I am keeping its default value of true since I want to be notified if the executable is missing. The WorkingDirectory property lets you specify a folder where the executable can be found. I prefer to leave WorkingDirectory blank and provide a fully qualified path to the executable in the Executable property.

The next step is to enter your arguments.

The Arguments property uses a plain text field. My sample application takes two arguments, which I’ll enter here. My sample application reads files that contain customer order information, filters out some data, and then stores the remaining data in another directory. Thus, both my arguments are file paths.

Although this works great for static arguments that won’t change, you may be wondering how to create arguments that can change each time the task is run. For example, let’s say you had an application that created log files and it took a date as an argument. You could create an argument, in the Execute Process task, that always passes in the current date as a string.

To do that, start by clicking on the Expressions item in the task dialog’s left ListBox.

Next, click on the Expander symbol to the left of the Expressions item.

That replaces the Expander box with an Expressions property and a field with an ellipses button. Click on the ellipses button to display the Property Expressions editor dialog box.

Using this dialog, you can assign SSIS expressions to various properties on this task. To set an expression on the Arguments property, click inside the selected Property cell.

The cell becomes a ComboBox. If you click on the down arrow, a dropdown list appears; select the Arguments property.

Next, click on the ellipses button at the end of the row to open the Expression Builder dialog box.

Here, you can author a SSIS expression whose value will be evaluated at runtime. Since I want to provide the date to an application, as a string, I would enter the following expression:

Notice that instead of entering a SSIS expression, I can also assign a Variable to an argument. Finally, if I need multiple arguments, one option would be to use an expression that uses multiple variables, separated by spaces.

Note that the Expression Builder dialog has some significant deficiencies. When entering an expression, you don’t get Intelliprompt support, and there is no syntax highlighting for your expression. Later on in this post, I’ll demonstrate how Mist has improved this experience.

Returning back to the Execute Process dialog, next up are the StandardInputVariable, StandardOutputVariable, and StandardErrorVariable properties. These all enable you to interact with an application’s input and output streams.

For example, my sample application prints the name of each file that’s processed. To store that output in a file on the command line, I would run SampleApp.exe > outputFile.txt. Using the Execute Process task, that output can be stored in a variable.

To do that, you need to assign a variable to the StandardOutputVariable property. Begin by clicking inside the field for the StandardOutputVariable property.

Next, click on the arrow button to open a dropdown.

Select the New variable… menu item to open the Add Variable dialog box.

This dialog allows you to set various properties on your new variable:

Name Description Container Indicates the scope of the variable. By selecting Package1, this variable becomes available to all containers, tasks, and event handlers within the package. Name Specifies a name for the variable Namespace SSIS provides two namespaces, System and User. The System namespace contains system variables and the User namespace contains custom variables. However, you are also allowed to create additional namespaces. Value type Specifies the type of literal that the variable stores. Note that the variable’s value must be of the value type. For example, having a variable value of abc with a value type of DateTime won’t work. Value The value that the variable stores. In order to make the purpose of this variable clearer, rename it to ProcessOutput. You can leave the remaining properties as-is.

Press OK to return to the task editor dialog box.

If the process you’re running prints to the error stream, you can repeat the above using the StandardErrorVariable property to capture error output in another variable.

As you may expect, the StandardInputVariable property lets you interact with a process’s standard input stream. On the command line, this is the equivalent of SampleApp.exe < inputFile.txt. For the task, instead of having input be stored in a file, the input would be placed in a variable’s Value field, and that variable would be assigned to the StandardInputVariable property.

Along with properties that control the executing process, the Execute Process task also lets you control what happens once the executable is running and how the executable’s behavior affects your workflow.

The SuccessValue property lets you specify the value that’s returned by your executable to indicate success. The default value is 0 and that’s the typical success value for most applications.

By setting the FailTaskIfReturnCodeIsNotSuccessValue property to true, the Execute Process task will compare the process’s return code to the value in SuccessValue. If they don’t match, the Execute Process task will fail.

For my scenario, I definitely want to be aware if my process fails so I will leave this property assigned to true.

The TimeOut property indicates how long your process can run, in seconds. The default value is 0, meaning the process can run indefinitely and won’t be stopped. If you set a value for TimeOut and the process is still running at that point, the process is allowed to finish but your task then fails.

If set to true, the related TerminateProcessAfterTimeOut property will cause the running process to be terminated if it runs past the task’s TimeOut value. You may have noticed that this property is initially disabled; it becomes enabled if you set a positive value for the TimeOut property.

If my process runs unexpectedly long, I want to be notified immediately. Thus, I am going to set the TimeOut property to 300, giving my process 5 minutes to run. To do that, I can simply click inside the TimeOut property’s text field and type in my value.

The final property in the dialog is the WindowStyle property, which controls how the process’s main window will appear when it’s executed.

Value Description Normal The process’s window will appear normally. Maximized The process’s window will be maximized. Minimized The process’s window will be minimized. Hidden You won’t be able to see the process’s window. Since I’m storing the process’s output in a variable, I am going to choose to hide my process’s window.

You can select a different window style by clicking inside the WindowStyle property field. A down arrow button will appear; click on it to open a dropdown.

You can then select a WindowStyle within the dropdown.

And with that, the Execute Process task is now ready to run.

Using Mist:

Now let’s see how to implement the Execute Process task in Mist.

To start, you’ll need to create a package. If you’re unfamiliar with how to create a package, you can follow the first couple steps in the Mist User Guide’s Creating a Package topic.

Once your package is open, navigate to the Toolbox tool window.

Select and drag the Execute Process task onto the Package’s design surface.

This task has a red X icon, just like in BIDS, since it has errors.

In past posts, at this point, I would point out the Package Details tool window and start entering values for the appropriate task properties. However, today I want to demonstrate the Biml editor.

Biml stands for Business Intelligence Markup Language, and is a XML language for describing BI assets. As you create assets in the Mist UI (packages, tasks, variables, expressions, cubes, tables, etc…), Mist generates Biml for each asset, which is saved when saving your project.

In Mist, the Biml editor is, by default, layered behind the designer.

If you click on the Package1.biml tab, it brings the Biml editor to the front.

Note that if you click and drag the Biml editor tab to the right, you can have the designer and Biml editor side-by-side.

In the Biml editor, start by clicking inside the ExecuteProcess element, right before its close tag element, e.g. /> Press your spacebar to see an Intelliprompt pop-up of all properties for the ExecuteProcess task.

Double click on the Executable property to insert it in the ExecuteProcess element.

Within the Executable attribute’s quotes, you can enter the path to your executable. Note that if you need to use the open dialog to find your executable, that’s available in the Package Details tool window via the ellipses button to the right of the Executable property’s text field.

Next, you can hit space bar, to the right of the end quote for the Executable path, to again display the properties list for the ExecuteProcess task.

If you single click on the Arguments property, notice that a pop-up appears that provides a brief description of the property’s purpose. Press the enter key to insert the Arguments property in the ExecuteProcess element.

You can type in your process’s arguments between the quotes.

To create a variable for the StandardOutputVariable property, click on the designer tab’s to return to the package’s designer.

With the ExecuteProcess task selected, click on the Variable button in the Mist ribbon.

This will add a variable to the ExecuteProcess task and will bring the Variable tool window to the front of the Mist UI.

The newly created variable is already selected. Matching the BIDS sample, rename this variable to ProcessOutput by double clicking on the variable’s Name cell and entering the new variable name.

Now, click on the Biml editor tab and notice that a Variables collection has been added in the Biml.

To tie this variable to your task’s OutputVariable, display the Intelliprompt pop-up again and double click on StandardOutputVariableName. Within the attribute’s quotes, enter the variable’s name.

You may have noticed that Mist filters already set properties from the Intelliprompt pop-up, to remove unnecessary items.

Matching the BIDS sample, you can keep using Intelliprompt to insert the Timeout property and set its value to 300, and then insert the WindowStyle property and set it to Hidden.

At this point, you could right click on Package1 in the Logical View tool window and run Build & Open in BIDS to build your Mist project and then open it in BIDS to run it.

Now, if you’re wondering about using an expression for the Arguments property, you can certainly do that in Mist as well.

To do that, first remove the Arguments property in the Biml editor.

Then, return to the package designer and in the Mist ribbon, click on the Expression button.

This creates an expression in the ExecuteProcess task and brings the Expressions tool window to the front of the window.

Double click on the expression’s Property cell to display a ComboBox. Click on the ComboBox to display its pop-up.

Select the Arguments property. Next, double click inside the property’s Expression cell.

Press the ellipses button to display Mist’s Expression Builder dialog.

One thing you’ll notice is the resize gripped down the middle of the dialog, and the additional resize gripped between the Expression and Result areas. This dialog is designed to give you lots more flexibility so you can focus on the areas you intended to work, and minimize the portions you don’t need.

If you click inside the Expression text area and start typing, you’ll notice that we immediately provide an Intelliprompt pop-up, along with a tooltip that describes what you’re entering. Also notice that expression text has color highlighting.

As I type in my expression, notice that I continue to see Intelliprompt.

If I press enter to select the DT_WSTR cast, it’s inserted in the Expressions text box with placeholders for the other values that are expected; in this case, the string’s length.

Once you’ve finished entering your expression, you can press the Evaluate button to obtain the expression’s value.

Notice that we display the expected and actual type of the expression’s value.

Once your expression is entered, press OK to dismiss the dialog. In the Biml editor, you’ll see that the expression is added, although formatted poorly.

To fix the formatting, go into the Biml Editor tab in the Mist ribbon, and press the Format Document button.

The Format Document button alters the document’s indentation to make it easier to read.

Extras:

One topic I didn’t cover here is getting the exit code from the application being run. You can check out this Stack Overflow post for details on how to do that.

Links:

Execute Process documentation for the Biml language

Execute Process task – MSDN

Craig