Data Driven Query Example: Changing Customer Accounts

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.

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:

• Insert
  
  
• Update
  
  
• Delete
  
  
• User

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.

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

UPDATE Account SET CompanyName = ? WHERE CustomerID = ?

DELETE FROM Account WHERE (CustomerID = ?)

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:

• CustomerID
  
  
• CompanyName

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.

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.

See Also

Lookup Queries

DTS Transformations

Data Driven Query Task