SQL Server 2000 DTS Part 10 - DTS Designer Tasks - The Data Driven Query Task

Friday Feb 27th 2004 by Marcin Policht
Share:

In the arsenal of DTS Designer Tasks, the one that leads in terms of versatility (and complexity) is the Data Driven Query Task. Its most distinctive feature, distancing it from other tasks, is its ability to not only handle inserts of rows during transformation, but also process any other T-SQL statements, such as UPDATEs, DELETEs, or stored procedures.

In the arsenal of DTS Designer Tasks, the one that leads in terms of versatility (and complexity) is the Data Driven Query Task. Its most distinctive feature, distancing it from other tasks, is its ability to not only handle inserts of rows during transformation, but also process any other T-SQL statements, such as UPDATEs, DELETEs, or stored procedures. This flexibility comes, however, with a performance price tag, so you should avoid it unless its added functionality makes its use justified or necessary. Insert operations are much more efficiently handled with previously discussed Transform Data and Bulk Insert tasks.

Before we look into implementation details and examples, let's make sure you understand the basic idea on which this extra functionality is based. You will shortly recognize that the "Data Driven Query" name aptly reflects the mechanism employed for data transformation in this case.

Transformation of each row of data between its source and target is divided into two main stages. Initial row processing is done via a custom ActiveX script. Logic implemented by the script examines each row from data source (in addition to performing whatever other tasks might be required) and depending on conditions you specify (for example, in the simplest case, match on a specific value from a particular field), it calls one of TSQL statements you created. TSQL statements are typically parameterized, with parameter values derived from a table called, in Data Driven Query parlance, a binding table. Typically, the same table functions also as the data target, although this is not mandatory (since the target table is the one specified within the TSQL statement and can be any table available via a DTS connection used by the Data Driven Query task). Note that actual change to the target table happens only as the result of execution of the selected TSQL statement (which might change from one data source row to another), with values of parameters derived from columns in the binding table (which, in turn, are derived from ActiveX script transformation of rows in the source table).

Now, that the basic premise of the task has been presented, let's discuss each of its components in detail (you can find their graphical representation on the Data Driven Query Task Properties dialog box) and provide a simple example illustrating their functionality:

  • Source - points to the data source for the ActiveX script based transformation (fields from its records appear as DTSSource entries), which, in addition to serving as data modification origin, is also used to determine the type of TSQL statement to be executed. This might be simply a value indicating the type of action (update, insert, deletion) or a value from which such information can be derived (e.g. date field, which can be compared against another reference date to remove outdated records). In addition, each source row includes fields used as parameters in TSQL statements, since source columns typically map to destination columns (within ActiveX script transformation).

  • Bindings - points to a data table containing fields used in an ActiveX script based transformation as DTSDestination entries and provides values for parameters in TSQL statements. As we mentioned before, actual changes to a target table do not take place as the result of code executed as part of the transformation ActiveX script, but rather as the result of execution of TSQL statements. This means that in the case where a binding table is different from a target table (target table is specified within the TSQL statement), its data is not modified at all. Most commonly, though, binding table also functions as the target table.

  • Transformations - defines transformation between the source and the binding table. By default, this is an ActiveX script executing as Row Transform function (Data Driven Query has multi-phase capabilities similar to the Multi-Phase Data Pump task discussed in our most recent articles). Its interface (including the Transformation Options dialog box) is practically identical to the one described previously (when covering Transformations feature of the Multi-Phase Data Pump task), although the Binding Columns tab is used instead of the Target Columns tab. However, differences in the ActiveX script structure are more significant. In particular, besides already familiar mapping between DTSSource and DSTDestination columns, ActiveX script provides the ability to launch one of four different TSQL statements for each row of data being processed. The statement to be launched depends on the value returned from the Row Transform function, represented by one of the following four constants:

    • DTSTransformStat_InsertQuery - indicating that an insert statement will be executed,
    • DTSTransformStat_UpdateQuery - indicating that an update statement will be executed,
    • DTSTransformStat_DeleteQuery - indicating that a delete statement will be executed,
    • DTSTransformStat_UserQuery - intended for any arbitrarily chosen statement type (including updates, inserts and deletes).

    Note that despite the fact that names of these constants seem to indicate the type of statement to be executed, they function purely as labels identifying which one of up to four TSQL statements you created should be invoked. The respective TSQL statements can actually perform any type of action, not necessarily the one matching the label.

  • Queries - contains four placeholders for parameterized TSQL statements. The placeholders are labeled Insert, Update, Delete, and Select and correspond to the four different return values from the ActiveX script (listed above). By default, parameters are populated in the order they appear in the query, using the binding table columns in sequence. You can list the parameter-to-column mapping by clicking on the Parse/Show Parameters command button (at that point, the mapping will appear in the lower portion of the dialog box). To change the default arrangement, simply click on the entry in the Destination column in the lower portion of the dialog box and select the binding table column you want to map to a specific parameter.

  • Lookups - as already described earlier in this series, from here you can retrieve a value or set of values via a DTS connection and use them within the ActiveX script transformation. Even though you can use existing source or destination connections, you should keep in mind that a separate lookup connection offers the best performance. Each lookup entry consists of its unique name, the connection name, cache setting (number indicating how many lookup results are cached), and SQL Query that defines the lookup operation. The query is defined with DTS Query Designer and can include one or more parameters (which, as usual, are designated by question marks and substituted with values derived from DTS global variables, when transformation is executed). For more information on the lookups and referencing them in ActiveX script, refer to our recent article.

  • Options - contains subset of settings (allowing specifying exception file and its format, as well as data movement parameters, such as first and last row, maximum error count and fetch buffer size) which we described in our previous article, when discussing Options tab on the Properties dialog box of Transform Data task.

Now it is time to present functionality of the described above components in a very simple example. Our data source will comprise of a text file containing entries to be used in order to modify content of the Shippers table in the Northwind database, in the following, semicolon-separated format:

Update;1;Super Speedy Express;(503) 555-9831
Update;2;Offshored Package;91 (123) 555-3199
Insert;4;Road Runner;(503) 555-1234
Insert;5;Speedy Gonzalez;(503) 555-4321
Delete;4;Road Runner;(503) 555-1234

Copy the five rows listed above to a text file and save it, (location and name are not relevant). As you can easily guess, the first field indicates the type of change to be applied, the second is the record (row) identifier, and the last two (Company Name and Company Phone) are used to create a new entry or update an existing one (they are obviously irrelevant in case of deletion). The row number 4 will be inserted and then shortly after deleted (purely for demonstration purposes). Note that we cannot simply delete already existing three Shippers rows because of the referential integrity rules. After the DTS task is completed, the first two rows should be modified and the fourth row should be added (with ShipperID identity column value set to 5).

We will use Shippers as our binding (as well as target) table. Since source and binding tables require separate connections, we will start by defining them. The first one will be of Text File (Source) type (you can locate it in the Connection menu or toolbar of the DTS Designer window). Specify the name of the file you just saved, make sure that you select Delimited file format, and choose semicolon as the column delimiter. Our target connection will be based on standard Microsoft OLE DB Provider for SQL Server pointing to the Northwind database on your test SQL Server.

Once connections are created, select the Data Driven Query task from the Task menu of the DTS Designer (or the toolbar area). Make sure that the Text File (Source) connection is listed on the Source tab (you can use the Preview button to verify that our sample data is properly interpreted). On the Bindings tab, choose the Northwind database connection and Shippers table. Delete default transformation on the Transformation tab. Click on the New button to define a new one and choose the ActiveX Script entry from the Create New Transformation dialog box. This will trigger display of the Transformation Options dialog box. On the Source Columns tab, select all columns (Col001 to Col004) and verify that the Binding Columns tab contains all entries from the binding table. On the General tab, click on the Properties button, which will display the following, automatically generated transformation script :

Function Main()
	DTSDestination("ShipperID") = DTSSource("Col001")
	DTSDestination("CompanyName") = DTSSource("Col002")
	DTSDestination("Phone") = DTSSource("Col003")
	Main = DTSTransformstat_InsertQuery
End Function

Replace it with the custom one, which will take into consideration the first column to determine the type of query to be invoked:

Function Main()
	Select Case UCase(Trim(DTSSource("Col001")))
		Case "UPDATE"
			DTSDestination("ShipperID") = DTSSource("Col002")
			DTSDestination("CompanyName") = DTSSource("Col003")
			DTSDestination("Phone") = DTSSource("Col004")
			Main = DTSTransformstat_UpdateQuery
		Case "INSERT"
			DTSDestination("ShipperID") = DTSSource("Col002")
			DTSDestination("CompanyName") = DTSSource("Col003")
			DTSDestination("Phone") = DTSSource("Col004")
			Main = DTSTransformstat_InsertQuery
		Case "DELETE"
			DTSDestination("ShipperID") = DTSSource("Col002")
			Main = DTSTransformstat_DeleteQuery
		Case ELSE
			Main = DTSTransformstat_UserQuery			
	End Select       
End Function

Confirm your changes by clicking on the OK buttons and return to the Data Driven Query Task Properties dialog box. On the queries tab, fill out the content of Insert, Update, and Delete query types with the following TSQL statements (one per type):

INSERT Shippers ([CompanyName],[Phone])
VALUES (?, ?)

UPDATE Shippers
SET CompanyName = ?, Phone = ?
WHERE (ShipperID = ?)

DELETE FROM Shippers
WHERE ShipperID = ?

For each statement, click on the Parse/Show parameters and verify that each parameter corresponds to an appropriate column from the target table (Shippers). Default mapping between parameters and destination columns can be modified by clicking on individual destination columns in the lower portion of the dialog box (this will be necessary for the Insert and Update statements). Finally, click on OK to close the Data Driven Query Task Properties dialog box, save your package, and execute it. You can verify that the content of the Shippers table has changed as expected using standard methods (Query Analyzer or Enterprise Manager):

1	Super Speedy Express		(503) 555-9831	
2	Offshored Package		91 (123) 555-3199	
3	Federal Shipping		(503) 555-9931	
5	Speedy Gonzalez		(503) 555-4321	

This simple exercise should give you the basis for creating your own packages including Data Driven Query task. For other examples, refer to the Books Online. We will complete our coverage of DTS tasks in the next article.

» See All Articles by Columnist Marcin Policht

Share:
Home
Mobile Site | Full Site
Copyright 2017 © QuinStreet Inc. All Rights Reserved