As we indicated in our previous article, we are going to continue discussing various ways of utilizing global variables in DTS Designer tasks. In particular, we will look into their role in exchanging data between packages, using features built into the Execute Package task. As part of this discussion, we will also explain the purpose of DTS Package Workflow.
The basic purpose of the Execute Package task is straightforward and clearly reflected by its name - it provides the ability to execute one package within another (we will refer to them throughout the rest of this article as child and parent packages, respectively). This promotes code reuse and modular design, especially in environments where DTS packages are used on a larger scale. By incorporating a frequently used function into a separate package, you can simplify creation and maintenance of packages that require it. Adding functionality to a new package is reduced to simply invoking an existing child package with proper parameters. Similarly, modifications no longer need to be applied wherever the functionality is used, but are limited to a single package.
Let's consider the following example. DTS offers the ability to record status and error information about package execution into a log (useful for troubleshooting), however, such data is always appended to an existing file. DTS does not provide any built-in ability to clear this log. In order to circumvent this limitation, you can develop a package that will delete a log file (this is relatively simple to implement using either ActiveX Script or Execute Process tasks) and include it in every package that you want to be able to monitor more closely. Another benefit of using Execute Package task is its ability to secure more sensitive features within a package by hiding their implementation (e.g. by taking advantage of password protection).
We will demonstrate how to implement the above-described scenario. Clearly, for this, two packages will be needed. To create the first one, right click on Local Packages node in the Data Transformation Services folder of SQL Enterprise Manager and select the New Package option from the context sensitive menu). A log file will be deleted with Execute Process task. From the Task menu (or Task portion of the toolbar on the left hand side of the DTS Package window), choose the Execute Process task item. In the Execute Process Task Properties dialog box, enter the following information:
Description: Delete DTS Log Win32 process: %ComSpec% Parameters: /C del C:\Logs\dts.log Return code: 0
The %ComSpec% notation designates the environment variable pointing to the location of the CMD.EXE. We use one of its internal commands - DEL, invoked as one of Win32 process parameters. In this case, the location of the log is C:\Logs\dts.log, but, obviously, this will likely need to be changed to match your configuration. Return code of 0 indicates successful completion. Timeout of 2 determines how long the process can run (in seconds) before it is terminated.
To test our package, save it as "Delete DTS Log," create a C:\Logs\dts.log text file (its content is irrelevant), and either execute the package (by clicking on the green triangle in the toolbar or selecting the Execute option in the Package menu), or execute the task (by right-clicking on it and selecting the Execute step option from the context sensitive menu). You should see a quickly flashing Command Prompt window and confirmation that the package or the step (depending on which one you launched) has been successfully executed. If you check the content of C:\Logs folder, dts.log should no longer be there.
Since we want to demonstrate the use of global variables for inter-package communication, we will now eliminate the hardcoded Win32 process name and its parameters. We will accomplish this using Dynamic Properties task, discussed in the previous article. From the Task menu, add Dynamic Properties task. In its Properties dialog box, click on the Add command button, expand Tasks node on the left hand side of the dialog box, select DTSTask_DTSCreateProcessTask_1 entry and choose the ProcessCommandLine item so it becomes highlighted. After you click on the Set button, you will be presented with the Add/Edit Assignment dialog box. In the Source list box, scroll down to the Global Variable item. From there, you will have an option to Create Global Variables. Activate it and enter the following parameters:
Name: gVarCmdLine Type: String Value: %comspec% /C del C:\Logs\dts.log
Once you click on OK, you will be back to the Add/Edit Assignment dialog box. From there, in the Variable list box of the Source information section, select the newly created gVarCmdLine. Finally, in the description of the task, replace the "Dynamic Properties Task:undefined" entry with a more appropriate one, such as "Set Command Line". After you close the dialog box, you should see two tasks in the DTS Package window - "Delete DTS Log" and "Set Command Line." We also need to ensure that the Dynamic Properties task window is executed first, since our variables need to be set prior to execution of the Win32 process. This can be done with precedence constraints, representing DTS Package Workflow.
DTS Package workflow reflects the sequence of execution of individual tasks within a package. There are three types of precedence constraints available that you can create between two tasks (for the sake of explanation, let's call them Task A and Task B, where Task B is supposed to follow Task A):
- Unconditional - Task B will execute as soon as Task A completes, regardless of its outcome,
- On Success - Task B will execute only if Task A completes successfully,
- On Failure - Task B will execute only if Task A fails (i.e. after it completes with an error).
Within the DTS Package window, precedence constraints are created by first selecting relevant tasks (in the sequence in which they should be executed) and then choosing the appropriate item from the Workflow menu. In our case, click on the Set Command Line icon first, then on Delete DTS Log, and select On Success Icon from the Workflow menu. Create a dummy log file again (C:\Logs\dts.log). Once you save and execute the package, you should see the same results as previously (Command Prompt window flashing quickly, the log file being removed, and the confirmation that the package executed successfully being displayed).
Now it is time to create a parent package, which will contain the Execute Package task. Start with the same procedure as before (select New Package option from the context-sensitive menu of the Local Packages node in Data Transformation Services folder of SQL Server Enterprise Manager). Once the DTS Package window is open, choose Execute Package Task from the Task menu (or Task area of the toolbar on the left hand side). In the Properties dialog box, specify the following:
Description: Execute Package Example Location: SQL Server Package name: Delete DTS Log
Authentication options within the dialog box will depend on your SQL Server settings. The package name (and version) can be either typed in (not recommended) or selected from the list of existing packages after clicking on the ellipsis (...) button. If "Delete DTS Log" package is the only one on the SQL server (and there is only a single version of it), its name will appear in the Package name text box. Otherwise, you will have an option of selecting it, along with a version (indicated by date and time of its creation). Typically, you should select the package name node (instead of a time-stamped entry), which indicates that its latest version should always be used. In the first case, you reference the package GUID, while in the second, the version GUID (both of them are displayed on the General tab of the package Properties dialog box). Using version GUID means that you will need to adjust the "Package name" option to point to the latest version every time you modify the package (assuming that you want to execute the most up-to-date version). If you want to remove reference to Version or Package GUID altogether (to avoid a problem in case a child package is replaced by another one with the same name), you can remove the Package ID entry using the Disconnected Edit feature (available from the Package menu in the DTS Package window). Once the Edit All Package Properties window is open, you can display Execute Package Task properties and remove the value of the PackageID entry.
The Execute Package Task Properties dialog box has two additional tabs - one for Inner, the other for Outer Global Variables. While initially both of them are empty, you can populate each using global variables from either inner (child) or outer (parent) package, respectively. For example, in our case, we can view (and modify) the value of gVarCmdLine variable (defined in the Delete DTS Log child package) on the Inner Package Global Variables tab. Similarly, if we had global variables defined in our parent package, we would be able to add them to the list on the Outer Package Global Variables tab (although it would not be possible to modify them from the Execute Package Task Properties dialog box - you have to do this from the Global Variables tab of the parent package Properties dialog box). It is important to point out that both inner and outer package variables are used to specify variables that will propagate to the child package. The main difference between them is that inner ones are set on the task level, while outer ones are set on the package level.
You can see immediately how the Inner Package Global Variables can be used. Simply select gVarCmdLine from the list box in the Variables section, and the rest of the entry will be automatically filled for you (including String type and "%comspec% /C del C:\Logs\dts.log" value). From here, you can also modify its value, if necessary. To use Outer Package Global Variable, start by removing reference to the variable from the Inner Package Global Variables tab (if both inner and outer package Variables are defined with the same name, the inner one will take precedence). Next, create a global variable within the parent package. As described before, this can be done from the Global Variables tab of the package Properties dialog box. In our case, you would define gVarCmdLine with an appropriate value. Finally, add the gVarCmdLine to the list of entries on the Outer Package Global Variables tab. Obviously, for this exercise to be complete, you should also enable logging for the parent package (by setting appropriate options on the Logging tab of the package Properties dialog box).
In our next article, we will continue coverage of the remaining types of DTS Package tasks.