Global variables are a little known solution in Data Transformation Services (DTS). Often packages become "stale", where
you must develop a package for each client. Using global variables, you can create one DTS solution for all clients and substitute any dynamic
properties with global variables. This article will cover the basics on how to use them in SQL Server 7.0. In SQL Server 2000, the role of global variables has been expanded. Global variables offer you the following options:
- Making a DTS package dynamic
- Passing variables to other packages
- Creating modular DTS packages
- Passing information from task to task
You can edit a package's global variables by clicking Properties under the Package menu in DTS Designer.
In this example, we will create a global variable called gvclientmessage and give it the string value of "Get lost world".
We will then create a small ActiveX script to show the variable in a dialog box. You can start by adding the variable:
You can call the global variable by using the DTSGlobalVariables object.
A dialog box can be raised with the global variable by typing the following:
Dialog=MsgBox(DTSGlobalVariables("gvclientmessage").value)
DTS global variables by default are case sensitive. Be careful to not mistype a single variable name or nothing will happen when you execute the package.
Your ActiveX script should now look like this:
Now, execute the package. You can execute the package under the Package menu. You should see the following:
Although this example is simple, it gives you the basics to calling gloabl variables
DTS global variables can also help you in many other ways, which we will explore next week.
Next week we will delve into a more advanced script to dynamically read a file and change the connection properties.
