Using Data Transformation Services: Using Global Variables with the DTS Designer

If you have ever wanted to create and use variables with global scope with the Microsoft DTS designer, this is the article for you. Two examples are used to illustrate the process, which involve deleting and updating records.

This tutorial deals with creating and using variables with global scope by utilizing the DTS designer. During package creation it is possible to create variables and assign them values that will have global scope. Variables with global scope created at design time can be used at run time while executing a package. For example, you can modify the data using global variables in a where clause. If a package is saved with the global variables defined and assigned, it will retain the value so defined. Using ActiveX scripts, it is possible to define a global variable at run time by defining the variable outside of a function. When a variable is defined inside a function, it will have only a procedural scope, unavailable after the function.

In Microsoft DTS, there are two ways to create/use global variables. It could be carried out at design time by specifying in the DTS designer (which is the gist of this tutorial) and dynamically by using ActiveX script, or by using the dtsrun utility with the /A command switch to define the variable.

Creating and using the global variables will be illustrated in this article with two examples outlining the major steps involved.  It is suggested that the reader review the previous DTS tutorials at the ASP Free site (http://www.aspfree.com).

{mospagebreak title=First example: Deleting Records}

In this first example, I have imported an Orders table from the Northwind database in SQL 2000 Server into my test bed database, TestWiz. The next picture shows the table from which two of the records for OrderDate 07/08/1996 will be deleted by defining the order date as a global variable.

In order to implement this deletion, I create a new package, GlobalVal as shown. By bringing up the property window of the package (menu item Package -> Properties), the properties for the package can be set, including the global variables. In the first tab General, you may give a description, and should you require, you may choose priority, and so on. Here, only a description has been added.

The next tab, Global Variables, is used to declare and assign value to a global variable. In the example, a global variable SD of date type has been declared with a value of 07/08/1996. The orders for this OrderDate need to be deleted. As you finish the first row, the second row drops down with default type of the next entry. If you want you may delete this row. The checkbox Explicit Global Variables is used, while global variables are going to be used in an ActiveX script. It’s similar to the Option Explicit in Visual Basic. Once you check this, all global variables have to be explicitly defined for the script not to throw an exception.

In the next tab, Logging, you can make choices to log the status of completion to the Event Log, and also any errors to an error file — a text file at a predetermined location as shown. The next tab, Advanced, has not been used in this tutorial.

We will be using the Execute SQL Task for the delete operation. This requires at least one connection to be specified. The next picture shows a connection to the SQL Server that will be used by the task.

Click on the Execute SQL Task in the collections pane and draw it out on the design pane. This creates the Execute SQL Task. By clicking on the Build Query…, the query can be built up by dragging the table to the query designer pane. By default this action would create a minimalist Select query which can be changed to a Delete query. In the next example a full discussion of constructing the query and testing it in-situ is provided. For now, the SQL Statement that will be executed is as shown in the next picture. This query, when given an order date, will delete rows corresponding to that date. The parametric query is expecting a parameter, the OrderDate.

The parameter specification is made by clicking the Parameters… button, which brings up the next window with two tabs, Input Parameters and Output Parameters. The present query requires only one parameter, the input parameter, which has already been set in the package’s Global Variable settings as shown earlier. This parameter would show up in the screen as shown here.

{mospagebreak title=Execution}

This package is now ready and can be executed. The package execution step is shown in the next two pictures. The first one shows how to execute the step and the second shows the progress bar of the execution process, as well as completion of the process.

 

The result of the execution is seen by bringing up the table in the Enterprise Manager, from which the orders were deleted. You will notice that the orders with order numbers 10250 and 10251 pertaining to the OrderDate 07/08/1996 have been deleted.

The next two pictures show the Event Log and the GlobalVal.txt after executing the package, items we created while configuring the package. Both of them provide similar information, and one of them persists to a file.

 

{mospagebreak title=Second Example: Update Records}

In this example, I will be using the same table, Orders, imported into my test bed database, TestWiz. The update query will update the RequiredDate and ShipName for the customer with CustomerID HANAR. It is assumed that all the RequiredDates have been postponed to 7/10/2005, and the shipping company changed the ship’s name to Hanari Banari from their existing values shown in the Orders table.

To implement this update procedure, we create a package as shown in the next picture, GlobalUpdate.

Since we are changing the value of three columns at the same time, we need three parameters which we specify in the Global Variables tab of the package as shown. Using the global variable sname we change the ship’s name. We also use SD, which now represents the RequiredDate and CID set equal to HANAR.

In the Logging tab we specify a error file with location, and also choose to write to the Event Log. You need only one of these, as it is essentially the same information.

 

The Update query will be fashioned using the DTS Designer. Create an instance of the Execute SQL Task by dropping it into the design pane. This brings up the next window shown in the picture. The SQL Statement text area will be empty to start with. What you see here is the result after building the query as described.

By clicking the Build Query, bring up the Query designer as shown in the next picture.

Click on the orders table in the listing on the left and drop it into the query design pane. By right clicking in the empty region of the query designer, you can pop up a menu from which you can change the query type from the default Select to the type of your choice. Here an Update type is chosen.

Then you can click the check box in the table definition in the query designer and create the query. You may have to add the ? marks shown as these are not added.

The query can now be tested in-situ by supplying the appropriate parameters as shown in this picture.

 

Once the query is created, click on the Parameters… button to bring up the Parameter Mapping window. Here, you make an association between preset Global variables with the parameters needed for the query as shown in the next picture. If the parameters are not set beforehand, you can create them using the Create Global Variables… button in this window. Since the Input Global variables were created in an earlier step, they should be available in the Input Global Variables tab in the Parameters Mapping window. Now the package is complete and it can be executed as before going to Package->Execute step.

{mospagebreak title=Execution again}

When the package is executed, you will come up with a execution progress bar; barring errors, you will get a success in the status window as shown. As shown previously you will also see a similar message in the Event Log as well as the error file you created earlier (both of them are not shown here).

Now, if you go back to Enterprise Manager, or Query Analyzer, and run a query to filter on CustomerID corresponding to HANAR, you will see the following as a result of the Update that has been made with this package.

 

Summary

It is relatively easy to declare and assign values to global variables in the definition of the package. It is also possible to assign and declare in the Execute SQL Task while specifying the parameters. In this tutorial, the global variables were defined in the definition of the package and later associated with the parameters of parameterized queries. It may be noted that, although datetime is the SQL data type, the drop-down in the global variables window of the package is just date.

One thought on “Using Data Transformation Services: Using Global Variables with the DTS Designer

  1. Most of the DTS tutorials on ASP free that I have written are related to using the DTS designer mostly and ActiveX script to a much lesser extent. However understanding the object model is most essential to go beyond what the designer supports. However, there are many more designer related topics that are not covered as yet. Those will be covered before taking a look at the object model.
    Again, I invite you discuss this article and the other related articles on ASP Free. I want to know if anything at all was useful. Do you need some other aspect discussed? Do you have questions on this tutorial? Did you notice any errors(of any type) in the tutorial?

[gp-comments width="770" linklove="off" ]