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

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.

Marcin Policht
Marcin Policht
Being a long time reader, I'm proud to join the crowd of technology gurus gathered here. I have a fair share of Microsoft exams behind me, a couple of acronyms after my name - MCSE, MCSD, and MCT, decent familiarity with majority of MS BackOffice products (SMS, SQL, Exchange, IIS), programming and scripting languages (VB, C++, VBScript with wsh) and several years of practical experience with Windows environment administration and engineering. My focus these days is on SQL Server and Windows 2000, and I'll attempt to share the most interesting experiences with these products.

Latest Articles