Data Driven Query Example: Changing Customer Accounts
The following example details an appropriate situation in which to use a Data Driven Query task: A source row triggers one of three different edits, two of which are not insert operations.
You have an Account table, with columns for CustomerID and CompanyName. CustomerID serves as a key:
CREATE TABLE Account ( CustomerID nchar (5) NOT NULL, CompanyName nvarchar (40) NOT NULL )
Required Account table changes accumulate in the AccountChange table. It contains CustomerID and CompanyName columns, as well as a ChangeCode column:
CREATE TABLE AccountChange ( CustomerID nchar (5) NOT NULL, ChangeCode nchar (10) NOT NULL, CompanyName nvarchar (40) NULL )
Different values of ChangeCode are used to request different Account table modifications.
|New||Add a new customer to the Account table.|
|Change||Change the CompanyName for an existing customer.|
|Delete||Remove a customer row from the Account table.|
The AccountChange table serves as the source. Each AccountChange row triggers one of three actions. These actions will be represented by INSERT, UPDATE, and DELETE queries.
Note In this example, all the changes were made to a single table. However, that is not a requirement of the Data Driven Query task. It requires only that the affected data all reside on the same connection.
Specifying Query Statements
After identifying an appropriate problem for the Data Driven Query task, you first specify up to four parameterized SQL statements to carry out required edit operations.
Each action requires an SQL query or a stored procedure invocation. These statements are parameterized by replacing, with a question mark, any expressions that vary from source row to source row. For example, two change actions might trigger the following commands:
UPDATE Account SET CompanyName = 'Big Pizza' WHERE CustomerID = 'MARS' UPDATE Account SET CompanyName = 'Tasty Gyro' WHERE CustomerID = 'ZEUS'
Parameterized, the UPDATE query reads:
UPDATE Account SET CompanyName = ? WHERE CustomerID = ?
At run time, the question marks will be replaced by values drawn from, or based on, source column data.
Stored procedure calls are parameterized like queries, with question marks replacing arguments:
sp_updatebalance ?, 'Credit', ?
Parameterized queries for the new and delete actions are as follows:
INSERT INTO Account (CustomerID, CompanyName) VALUES (?, ?) DELETE FROM Account WHERE (CustomerID = ?)
Assigning Query Types to Statements
In order to refer to your SQL statements, you assign each statement a name, called a query type. A query type, returned by your Microsoft® ActiveX® script code, is used to select one of your SQL statements to execute. Data Transformation Services (DTS) provides the following four names:
These query types should be viewed only as unique identifiers assigned to each statement. It is in fact possible to perform any SQL operation supported by the connection. It would be possible for example, to perform four different updates, four different inserts or any mix of these or stored procedures.
The example is one of those applications in which the available query types match the parameterized SQL statements. Therefore, the assignments are as follows.
|Query type||Parameterized Query|
Specifying the Binding Table
After you assign query types to your SQL statements, you need to specify a binding table whose columns match the parameters in your parameterized query. This binding table allows you to transform source data before it appears in your SQL statements. For example:
- A customer name can be uppercase characters.
- An address line can be constructed by concatenating several fields.
- City information can be looked up, given a postal code in the source data.
The Data Driven Query task makes no actual changes to the destination unless you specifically request them in your queries.
To specify the binding table, list all the parameters required by your queries. Then, review your existing tables to see if any contain all the columns in your list. If you find such a table, it can serve as the binding table. If no existing table contains all of the required parameters, create a new table that does.
Note The Data Driven Query task requires that the source table and binding table use different connections.
For example, the preceding queries use two parameters:
A review of the database yields two tables that contain both columns: Account and AccountChange. Because AccountChange is likely to serve as the source, Account is the better choice for the binding table. No new table is necessary.
In this example, the binding table is the same table that the queries update. This frequently happens but is not required by the Data Driven Query task, as the binding table exists only to map meta data (size, scale, precision, and nullability) for the queries. It is not actually written to. Only the query operation affects the data
Specifying the Source Rowset
After specifying the binding table, you either must choose an existing table as a source or specify a new source rowset. Each source row must contain enough information to:
- Determine the appropriate query to execute.
- Fill any parameters required by the chosen query.
If this information is available in a single table, it can serve as the source. If not, you can create an SQL query to collect required information in a single source rowset.
If necessary, custom ActiveX script code, perhaps referencing DTS lookup queries, can be used to help determine the proper query to execute. Source data can be copied immediately into binding column parameters, or the data may undergo intermediate processing through ActiveX code or DTS custom transformations.
In the example, the AccountChange table will serve as the source. It fulfills both requirements:
- The ChangeCode column determines the choice of query.
- The CustomerID and CompanyName columns are sufficient to fill all required parameters.
Specifying the ActiveX Transformation
To choose which query to execute, you must code a single ActiveX transformation. This script returns one of four values, which is then used to select the query to execute. Additionally, you may choose to include ActiveX code to populate destination parameters.
The return values and their associated query types are as follows.
|Return value||Executes Query Type|
These query types should be viewed only as identifiers for one of your queries. You can assign, for example, the Insert type to a DELETE query. If your script returns DTSTransformstat_InsertQuery, the DELETE query will be triggered.
Usually your code takes the form of a nested IF or SELECT CASE structure. For example, to choose among three queries based on the value of ChangeCode, use the following code:
Select Case Trim(DTSSource("ChangeCode")) Case "New" Main = DTSTransformStat_InsertQuery Case "Change" Main = DTSTransformStat_UpdateQuery Case "Delete" Main = DTSTransformStat_DeleteQuery Case Else Main = DTSTransformStat_SkipRow End Select
The above code responds to erroneous ChangeCode values by returning DTSTransformStat_SkipRow. No query is triggered for the source row.
You can use a Copy Column transformation or other column-level transformations to populate binding table columns, or you can fill them through additional code in your ActiveX transformation:
DTSDestination("CustomerID") = DTSSource("CustomerID") DTSDestination("CompanyName") = DTSSource("CompanyName")
You are not required to fill every destination column, only those required by the chosen query.