SQL Server 2000 DTS Part 5 - DTS Designer Tasks and Global Variables
December 9, 2003
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:
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.