Database Journal
MS SQL Oracle DB2 Access MySQL PostgreSQL Sybase PHP SQL Etc SQL Scripts & Samples Tips Database Forum Rss Feed

» Database Journal Home
» Database Articles
» Database Tutorials
MS Access
SQL Scripts & Samples
» Database Forum
» Slideshows
Free Newsletters:

News Via RSS Feed

Rss Feed

Database Journal |DBA Support |SQLCourse |SQLCourse2

Featured Database Articles


Posted Dec 9, 2003

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

By Marcin Policht

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:

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

MS SQL Archives

Latest Forum Threads
MS SQL Forum
Topic By Replies Updated
SQL 2005: SSIS: Error using SQL Server credentials poverty 3 August 17th, 07:43 AM
Need help changing table contents nkawtg 1 August 17th, 03:02 AM
SQL Server Memory confifuration bhosalenarayan 2 August 14th, 05:33 AM
SQL Server Primary Key and a Unique Key katty.jonh 2 July 25th, 10:36 AM