Using Data Transformation Services: Using Global Variables with the DTS Designer - First example: Deleting Records
(Page 2 of 5 )
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.
Next: Execution >>
More MS SQL Server Articles
More By Jayaram Krishnaswamy
/ 14 
