Data Driven Query Example: File Maintenance

The following example presents a file maintenance problem and then examines the steps necessary to prepare a Data Driven Query task solution.

For more information about the basics of the Data Driven Query task, see Data Driven Query Task.

Identifying a Data Driven Query Problem

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 )

You also have an AccountJournal table. One row is written to this table each time there is a change in the customer balance:

CREATE TABLE AccountJournal (
   UpdateID int NOT NULL,
   CustomerID char (5) NOT NULL,
   JournalAmount money NOT NULL )

You want to use the AccountUpdate table as a source table:

CREATE TABLE AccountUpdate (
   UpdateID int IDENTITY (1, 1) NOT NULL,
   UpdateCode char (10) NOT NULL,
   CustomerID char (5) NOT NULL,
   CompanyName char (30) NULL,
   UpdateAmount money  NULL )

Different values of UpdateCode are used to request different Account and AccountJournal modifications.

UpdateCode	Required Action
Purchase	Write a new AccountJournal row with JournalAmount set equal to UpdateAmount.
Payment	Write a new AccountJournal row with JournalAmount set equal to UpdateAmount * -1.
New	If a customer does not exist, add one. If the customer exists, change the old CompanyName to the new one.

A Data Driven Query task is appropriate for this problem. Each entry in the AccountUpdate table triggers one of three different queries.

Specifying Query Statements

Three parameterized SQL statements are required to solve this problem:

INSERT AccountJournal (UpdateID, CustomerID, JournalAmount) 
   VALUES (?, ?, ?)
INSERT INTO Account (CustomerID, CompanyName) VALUES (?, ?)
UPDATE Account SET CompanyName = ?  WHERE CustomerID = ?

The first query adds a row to the AccountJournal table in the case of a "Purchase" or a "Payment"; the second query adds a new account if the customer was not previously on file; and the last updates the CompanyName for an existing customer.

Assigning Query Types to Statements

There are two INSERT queries and one UPDATE query. This means that query types are not going to match the actual query content.

Query type Parameterized Query

Insert

Query type	Parameterized Query
Insert	`INSERT AccountJournal (UpdateID, CustomerID, JournalAmount)` `VALUES (?, ?, ?)`
Update	`UPDATE Account SET CompanyName = ? WHERE CustomerID = ?`
User	`INSERT INTO Account (CustomerID, CompanyName) VALUES (?, ?)`

INSERT AccountJournal (UpdateID, CustomerID, JournalAmount) 
   VALUES (?, ?, ?)

Update

UPDATE Account SET CompanyName = ?  WHERE CustomerID = ?

User

INSERT INTO Account (CustomerID, CompanyName) VALUES (?, ?)

The second INSERT query is assigned arbitrarily to the User query type. The Delete type would work just as well.

Specifying the Binding Table

The binding table provides names and data types for your SQL parameters. Your queries use the following parameters:

UpdateID
CustomerID
JournalAmount
CompanyName

There is no table in your database that contains all four of these columns. Therefore, you must create a new binding table:

CREATE TABLE AccountDestination (
   UpdateID int NOT NULL,
   CustomerID char (5) NOT NULL,
   CompanyName char (30) NULL,
   JournalAmount money NULL )

No rows will ever be written to this table. Its only function is to provide an empty row to serve as a staging area for the SQL statement parameters.

Specifying the Source Rowset

The AccountUpdate table is not ready to serve as a source table:

An UpdateCode of "New" triggers an UPDATE or an INSERT, depending on whether the customer is on file or not. However, customer-on-file status is not present in the AccountUpdate table.
JournalAmount, a binding column, is not found in AccountUpdate. For purchases, it is equal to UpdateAmount, but for payments, it must be calculated by multiplying UpdateAmount by –1.

Incorporating the customer-on-file status into the source rowset, by using a SELECT statement, solves the first of these two problems. The new source SQL statement initializes OnFile with a subquery:

SELECT UpdateID, UpdateCode, CustomerID, CompanyName, UpdateAmount, 
   OnFile = (SELECT COUNT(*)FROM Account 
                WHERE CustomerID = AccountUpdate.CustomerID)  
   FROM AccountUpdate

The second problem is solved with Microsoft® ActiveX® code.

Specifying the ActiveX Transformation

In this example, Data Transformation Services (DTS) transformations perform the following three jobs:

Correctly choose the query to execute.
Compute the right value for JournalAmount.
Populate the required binding table columns.

A single ActiveX Script transformation is sufficient to carry out these requirements:

Function Main()
   DTSDestination("UpdateID") = DTSSource("UpdateID")
   DTSDestination("CustomerID") = DTSSource("CustomerID")
   DTSDestination("CompanyName") = DTSSource("CompanyName")
   Select Case Trim(DTSSource("UpdateCode"))
      Case "Purchase"
         DTSDestination("JournalAmount") = DTSSource("UpdateAmount")
         Main = DTSTransformstat_InsertQuery
      Case "Payment"
         DTSDestination("JournalAmount") = -1 * DTSSource("UpdateAmount")
         Main = DTSTransformstat_InsertQuery
      Case "New"
         If DTSSource("OnFile") = 1 Then
            Main = DTSTransformstat_UpdateQuery
         Else
            Main = DTSTransformstat_UserQuery
         End If
   End Select      
End Function

The script first initializes three binding table columns, and then, in a SELECT CASE statement, fills the remaining parameter and sets the return value. Both the "Purchase" and "Payment" cases result in a newly inserted AccountJournal record. The two cases differ only in how the JournalAmount parameter is calculated. In the "New" case, the source OnFile value is used to determine whether to update an existing customer or insert a new customer. Neither of the two possible queries requires JournalAmount, so it is not initialized.