How to execute a Process Task where the Executable path comes from a user Variable

Question

I have an SSIS package that includes a Process Task. The process task needs to execute batch file. The location of the batch file varies depending on the environment (dev, production).

How do I dynamically set tghe value of the Executable property of the Process Task object to a user variable?

Answer 1

I think that you need to create a variable that takes in the folder path and the executable file name to evaluate as an expression. You then need to assign this variable that evaluates as expression to the Executable property of the Execute Process Task. Also, you need to have a valid executable path during design time.

This is a crude step-by-step example that shows how you can pass a variable to the Executable property of Execute Process Task. The example was created using SSIS 2008 R2 and also uses three .bat files to illustrate the functionality. Even though the example is in SSIS 2008 R2, the logic should be applicable to SSIS 2005.

Create three .bat files namely Process_0.bat, Process_1.bat and Process_2.bat. Refer screenshot #1. Populate them with echo commands as shown in screenshots #2 - #4.

Create an SSIS package. I have named the package in the format YYYYMMDD_hhmm in the beginning followed by SO stands for Stack Overflow, followed by the SO question id, and finally a description. This is for me to easily refer this back later. Refer screenshot #5.

Create the following variables: Refer screenshot #6.

• ExecutableFileName - This variable is of type string. It will contain the executable file name.

• ExecutableFolder - This variable is of type string. It will contain the executable file' folder path.

• ExecutableFilePath - This variable is of type string. Do not key in any value for this variable. This will be an expression that combines the variables ExecutableFolder and ExecutableFileName to generate the full file path. Refer screenshot #7. Select the variable and press F4 to open the properties window. Set EvaluateAsExpression to True and set the Expression to @[User::ExecutableFolder] + @[User::ExecutableFileName]

• ExecutableOutput - This variable is of type string. It will store the executable's output value. In this case, the value echoed by the .bat files.

On the package's control flow path, place Execute Process Task and a Script Task as shown in screenshot #8.

Configure the Execute Process Task as shown in screenshot #9 - #11. On the Process section of the Task, you need to specify an Executable path for initial configuration. Also, specify the StandardOutputVariable (this is for this example). On the Expressions section, override the Executable path by specifying the variable @[User::ExecutablePath]

On the Script Task, replace the Main method as shown in screenshots #12 - #13.

Execute the package. You should get the output as shown in screenshot #14. It shows the .bat file Process_0.bat was executed with the output "Process 0".

Now, change the value of the variable ExecutableFileName to Process_1.bat. Do not make any other changes. Execute the package. You should get the output as shown in screenshots #15 - #16. It shows the .bat file Process_1.bat was executed with the output "Process 1".

Now, change the value of the variable ExecutableFileName to Process_2.bat. Do not make any other changes. Execute the package. You should get the output as shown in screenshots #17 - #18. It shows the .bat file Process_2.bat was executed with the output "Process 2".

Hope that helps.

Screenshots:

#1:

#2:

#3:

#4:

#5:

#6:

#7:

#8:

#9:

#10:

#11:

#12:

#13:

#14:

#15:

#16:

#17:

#18: