SQL Server 2000 DTS Part 6 – DTS Designer Tasks and Package Workflow

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:Logsdts.log
Return code:   0
Timeout:       2

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:Logsdts.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:Logsdts.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:Logsdts.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:Logsdts.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:Logsdts.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.

»


See All Articles by Columnist
Marcin Policht

Marcin Policht
Marcin Policht
Being a long time reader, I'm proud to join the crowd of technology gurus gathered here. I have a fair share of Microsoft exams behind me, a couple of acronyms after my name - MCSE, MCSD, and MCT, decent familiarity with majority of MS BackOffice products (SMS, SQL, Exchange, IIS), programming and scripting languages (VB, C++, VBScript with wsh) and several years of practical experience with Windows environment administration and engineering. My focus these days is on SQL Server and Windows 2000, and I'll attempt to share the most interesting experiences with these products.

Latest Articles