Chapter 13

Using the Execute Process Task

When creating SSIS packages, you will sometimes find that you need to run a process or executable outside of your SSIS package. A good example of this is the need to compress or decompress files for a package before loading the data from those files into a database. There is no built-in compression task in the SSIS Toolbox. The Execute Process Task enables you to call these windows or console applications.

Errors that occur in the outside programs can be captured in the SSIS package in a variable. This variable value can be written to a log file or a table for auditing.

The Execute Process Task is shown in Figure 13-1, where the Process node is selected on the left. In this screen, you select the executable the task will call and enter any arguments you need to pass to the executable. Arguments can be thought of as parameters and are not always required by the executable. The figure has an executable set and an argument for example purposes.

You can see several other properties in the Process node of the Execute Process Task. The following list explains each of these properties.

• RequireFullFileName—Tells the task whether it needs the full path to execute the command. If the file is not found at the full path or in the PATH environment variables of the machine, the task will fail. Typically, a full path is used only if you want to explicitly identify the executable you want to run. However, if the file exists in the System32 directory, you wouldn’t normally have to type the full path to the file because this path is automatically known to a typical Windows system.
• Executable—Identifies the path and filename for the executable you want to run. Be careful not to provide any parameters or optional switches in this property that would be passed to the executable. Use the Arguments property to set these types of options separately. For example, Figure 13-1 shows that the task will execute PingParameter.bat and pass in the site to ping, which in this case is www.bing.com.
• WorkingDirectory—Contains the path from which the executable or command file will work.

Figure 13-1

• StandardInputVariable—Variable used to pass into the process as an argument. Use this property if you want to dynamically provide a parameter to the executable based on a variable.
• StandardOutputVariable—Captures the result of the execution by setting the StandardOutputVariable property to a variable.
• StandardErrorVariable—Any errors that occurred from the execution are captured in the variable you provide in this property.

The variables mentioned in the preceding list can be sent to an Execute SQL Task to log or can be used in a precedence constraint later in the package that checks the length of the variables to determine whether you should go to the next task. This enables you to loop back to the process task again if need be.

Other options in the Process tab include:

• FailTaskIfReturnCodeIsNotSuccessValue property—Another option for validating if the executable completed successfully.
• SuccessValue option—The Execute Process Task will fail if the exit code passed from the program is different from the value provided in the SuccessValue option. The default value of 0 indicates that the task was successful in executing the process.
• Timeout/TerminateProcessAfterTimeOut properties—The Timeout property determines the number of seconds that must elapse before the program is considered a runaway process. A value of 0, which is the default, means the process can run for an infinite amount of time. This property is used in conjunction with the TerminateProcessAfterTimeOut property, which if set to true terminates the process after the timeout has been exceeded.
• WindowStyle option—You can set the executable to be run minimized, maximized, hidden, or normal. If this is set to any option other than hidden, users will be able to see any windows that potentially pop up and may interact with them during run time. Typically, these are set to hidden once a package is fully tested and deployed to a server to be run on a schedule unattended.

With the Execute Process Task, you can use command-line or out-of-process executables to perform work for ETL tasks. This extends SSIS beyond just what can be accomplished in the Toolbox.

The code supplied with this lesson on the book’s website at www.wrox.com contains two batch files that are set up to ping a URL. The one named PingBing.bat is hard-coded to ping the URL www.bing.com. The following is the code used in this batch file:

ECHO Start Ping of Bing
PING www.Bing.com 
ECHO Finished Ping of Bing

In the second batch file named PingParameter.bat the code is set to ping the argument passed to the executable. It is almost identical except that it uses a parameter instead of the hard-coded site name. The following is the code found in this batch file:

ECHO Start Ping of %1
PING %1
ECHO Finished Ping %1

There is also an SSIS package in the code with this book that contains two Execute Process Tasks, each one calling one of the batch files just described. In the following section, you build a package to call the PingParameter.bat file. You will need Internet connectivity for the batch file to ping the URLs.

Try It

In this Try It, you create an Execute Process Task to ping a website. This will show success when the task is able to ping the website. After this lesson you should have an understanding of how you can use the Execute Process Task to extend the capability of SSIS.

You can download the completed Lesson13.dtsx and the two batch files mentioned earlier in this lesson from www.wrox.com.

Lesson Requirements

You need to create an Execute Process Task. The executable information needs to be the name of the bat file. The argument can be changed to any website.

Hints

• You need one Execute Process Task.
• You need the bat file that is included in the code with this book.

Step-by-Step

Figure 13-2

Figure 13-3

Figure 13-4

Feel free to change the value of the variable and execute the package again and you should see it ping the sites you enter.