Execute Process Task

The Execute Process task will execute a Windows or console application inside of the controller flow. It's been much improved on since SQL Server 2000, and now it is more robust in its error handling. For example, you can now write any errors from the execution to a variable that can be read later. Also, any output from the command file can be written to a variable.

The Process page in the Execute Process Task Editor (shown in Figure 3-7) contains most of the important configuration items for this task. The RequireFullFileName property tells the task whether it needs the full path to execute the command. If the file is not found at the full path, the task will fail. This is useful only when you're worried about a second copy of the file existing in a folder somewhere that is referenced by the Windows PATH. For example, if the file exists in the System32 directory, you wouldn't have to type the full path to the file.

Figure 3-7

The Executable option is the path and file name to the executable you'd like to run. This does not contain any types of switches or parameters you'd like to pass. The Arguments option contains those types of items. For example, Figure 3-7 shows that the task will execute expand.exe and pass in the cabinet from which you want to extract and where you'd like it to be extracted. The WorkingDirectory option contains the path from which the executable or command file will work.

The StandardInputVariable parameter is the variable you'd like to pass into the process as an argument. It is a way to make that one option dynamic. You can also capture the result of the execution with the StandardOutputVariable and any errors that occurred from the execution in the StandardErrorVariable. You can later use this to determine if you should go to the next task by using expression validation in the precedence constraint. For example, if the result of the execution of the expand.exe program was a sharing violation, you can loop back to the beginning and try again.

There are some other minor options in this page of the editor. One of those is the FailTaskIfReturnCodeIsNotSuccessValue option, which indicates that the task will fail if the exit code passed from the program is different from the SuccessValue option. The default value that indicates success is 0. The Timeout option determines the number of seconds that must elapse before the program is considered a runaway process and has stalled. A value of 0, which is the default, means the process can run for an infinite amount of time. This option is used in conjunction with the TerminateProcessAfterTimeOut option, which, if set to true, will terminate the process after the timeout has been surpassed. The last option is WindowStyle, which can set the executable to be run minimized, maximized, hidden, or normal. If this is set to any option other than hidden, the user will be able to see windows potentially pop up and may interact with them if the user catches them in time.