SQL Server 2000 DTS Part 5 – DTS Designer Tasks and Global Variables

In the previous article of this series, we covered types of DTS Designer
tasks that are more straightforward. We will continue this discussion here,
focusing on ones that are more complex. However, before we proceed, we will
look into the concept of DTS Global Variables, which will help us understand
some of the options available when dealing with task configuration.

A global variable is a package component consisting of a name and a value of
a specific type (string, integer, etc.) that is associated with that name.
Global variables provide temporary storage for values used within DTS packages.
If you are familiar with scripting or programming, you can easily relate to
this idea, since variables have the same purpose when they are incorporated
into executable code. It is worth noting that names of global variables are
case sensitive, since this might be a source of confusion if you ever need to
reference the same variable from two packages (we will see examples of it when
discussing the Execute Package task). Global variables are accessible via
graphical interface from the Global Variables tab of a package Properties
dialog box. You can create, modify, and delete variables from here (you can
also access this interface from the Parameter Mapping dialog box of Execute SQL
Task or the Add/Edit Assignment dialog box of Dynamic Properties Task). The
same can be accomplished via ActiveX scripting, which we will cover in our
future articles (and explain the meaning of the Explicit Global Variables check
box on the package Properties dialog box). The main difference between
variables created with graphical interface and the scripted ones is their
duration. In the first case, global variables can be saved as part of the
package, can be accessed from any package component throughout its execution,
and can be viewed after the package is finished running (as long as the package
has been saved). In the second case, their lifetime is limited to the script
execution (and they are neither visible from the Global Variables tab nor
available after the package completes).

Let’s take a look at an example demonstrating basic use of global variables
in combination with Execute SQL Task, briefly discussed in our previous
article. Imagine that you need to determine the most recent order from a
particular client within a specific time period, stored in Orders table of the Northwind
database. Even though, at this stage, such an example has rather limited
practical value (in essence, it is simply a single T-SQL statement), it should
be sufficient for this introduction (we will be covering more complex examples
later in this series). We could use a similar approach if, for example, we
wanted to transfer (and transform) extracted order information to an Oracle
database (although, obviously, this would require some modifications to our
example).

Start by creating a new package from the SQL Server Enterprise Manager
interface (select New Package from the context sensitive menu of Local Packages
node in the Data Transformation Services folder of the SQL Server Enterprise
Manager). Configure a new connection to the Northwind Database using Microsoft
OLE DB provider for SQL Server (specify authentication settings, choose Northwind
in the Database list box, and assign the Northwind DB name to it). Next, select
Execute SQL Task from the Task menu (or the Task portion of the toolbar on the
left hand side). This will display the Execute SQL Task Properties dialog box.
In the Description field, type in Select MAX OrderID (or any other name
sufficiently indicating the purpose of this task), confirm that Northwind
Database is selected in the Existing connection list box, leave 0 as the
Command time-out value (which determines how long the task is allowed to
execute), and in the SQL Statement text box, enter the following:

SELECT MAX(OrderID)
FROM Orders
WHERE RequiredDate BETWEEN ? AND ?
AND CustomerID = ?

You might recall from the previous article that the question mark in the
context of the Execute SQL Task designates an input parameter. At this point,
we have three parameters (Parameter 1, Parameter 2, and Parameter 3,
corresponding to the first, second, and third question mark). Now, we need to
link parameters to global variables, to which we will then assign arbitrary
values. This is done by clicking on the Parameters button on the General tab of
the Execute SQL Task Properties dialog box, which will display the Parameter
Mapping dialog box. After clicking on the Create Global Variables button, you
will be able to enter four variables (three for input parameters and one for
output) and specify type and value for each. (Note that a value is required,
since if you try to create a variable of non-string data type without it, you
will receive the error message stating "could not convert variable XXXX
from type BSTR to type YYYY", where XXXX and YYYY are variable name and
type you chose). You can use the following data:

gVarDateFrom   String  1998-06-02
gVarDateTo     String  1998-06-03
gVarCustomerID String  BONAP
gVarMaxOrderID Integer 0

Once you click on OK, you will be presented again with the Parameter Mapping
dialog box. From here, assign the Input Global Variables to Parameters in the
following fashion (using Input Parameters tab):

gVarDateFrom   Parameter 1
gVarDateTo     Parameter 2
gVarCustomerID Parameter 3

Next, switch to Output Parameters, set Row Value to Output Parameter Type, and
specify gVarMaxOrderID in the Output Global Variables column. Close the active
dialog boxes, save, and execute the package. Display the DTS Package properties
window and switch to the Global Variables tab. You should see all the variables
we have defined with their values. gVarMaxOrderID should be set to 11076
(assuming that all other values are identical to the ones used in this
example). As you can see, global variables retain their values after package
execution. We can also conveniently change them from a single location, if we
want to rerun the package for a different client or a date range. Clearly, you
can modify values of global variables manually; however, you can also
accomplish the same through scripting. This allows you to further simplify, or
even fully automate, modifications of packages.

An alternative approach to scripting, when dealing with automating changes
to package parameters, involves the use of Dynamic Properties task. Dynamic
Properties task allows such modifications at run time, but is configurable via a
graphical interface. You can control practically any DTS component – such as
connections, tasks, steps (we will talk more about steps once we start
discussing workflow component of DTS packages), and global variables. Values of
properties for each of these components can be derived from one of the
following sources:

  • INI File – a text file in the standard format, divided into
    sections (with headers enclosed in square brackets) and entries in each
    section, consisting of keys/value pairs, each on a separate line. When using
    this option, you need to provide full file path, section, and key, which value
    will be used by the task

  • SQL Query – a valid SQL SELECT statement utilizing an existing
    connection. This needs to be a scalar value, otherwise only the first column of
    the first row in the returned recordset is used

  • Global Variable – this allows you to modify any of the parameters
    by simply specifying the name of a global variable. For example, to allow
    package portability, you can assign a global variable (and appropriate variable
    value) to DataSource property (indicating target SQL server name). Once a
    package is moved to a different server, you can simply modify this global
    variable on the Global Variables tab of the package properties, without
    resorting to recreating a new connection.

  • Environment Variable – system and user Windows environment
    variables (the same ones you can view by clicking on the Environment Variables
    button on the System Properties dialog box)

  • Constant – an individual value defined and stored within the task

  • Data file – content of a file (unlike in the case of INI File, the
    entire file is used)

With external files or environment variables, you can modify parameters of a
package without editing it directly, which might further simplify its
portability. Note that Dynamic Properties task should be launched at the very
beginning of a package execution (we will discuss in future articles how this
can be controlled using Workflow elements of a package). This way, you ensure
that any of the properties referenced within the package are set to proper
values before they are used.

Global variables accommodate exchange of data not only between elements of
the same package, but also between separate packages. This functionality is
employed in Execute Package Task, where one package is invoked within another.
We will start our next article by discussing its properties and examples of its
usage.

»


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