Adding DTS Transformations

DTS Programming

DTS Programming

Adding DTS Transformations

Data Transformation Services (DTS) transformations are used by the data pump to perform various operations that you specify. The data pump is the engine for the DataPumpTask2, DataDrivenQueryTask2, and ParallelDataPumpTask objects. Transformations can be viewed as callbacks from the data pump. The other task classes supplied with Microsoft® SQL Server™ 2000, which do not host the data pump, do not use transformations.

The data pump fetches data rows from a source connection and writes data rows to a destination connection. The table below describes the phases of the data pump operations for which transformations can be specified. They are listed in the order in which they are invoked by the data pump.

Phase Description Possible Uses
PreSourceData Occurs before first row is fetched from source connection. Writing header records to the destination.

Initializating objects, connections or memory for use in later phases.

Transform Occurs after each source row is fetched, before the destination row is written. Converting data types.

Validating.

OnTransformFailure Occurs after a failure in the Transform phase, indicated by the return of DTSTransformStat_Error or DTSTransformStat_ExceptionRow. Typically caused by conversion errors. Handling custom data based on the Transform failure.
OnInsertSuccess Occurs after each data row is written successfully to the destination connection. Maintaining aggregation when this function cannot be done by a Transform phase transformation.
OnInsertFailure Occurs after each attempt to write a data row to the destination connection failed (for example, by attempting to write a duplicate value to a primary key field, or a null to a NOT NULL field). Handling custom data based on the Insert failure (for example, writing the data to an error table).
OnBatchComplete Occurs in DataPumpTask2 when using FastLoad option after each batch is written, successful or failed. Recording the current position within the source rowset, which could then be used as the starting point if the task needed to be restarted.
PostSourceData Occurs after the last row is written to the destination connection. Writing trailer records to the destination or freeing up resources or committing data held in global variables.
OnPumpComplete Occurs at the end of the task execution. Freeing up resources or committing data held in global variables.

In the case of the ParallelDataPumpTask, the PreSourceData and PostSourceData phases occur at the beginning and end, respectively, of each constituent rowset of the hierarchical rowset. The OnPumpComplete phase occurs once.

The DTSTransformScriptProperties2 transformation can support multiple phases. You provide a script function for each supported phase. Transformations for phases other than Transform must be DTSTransformScriptProperties2 transformations or custom transformations.

Creating Transformation Objects

To implement a transformation, you need a generic Transformation2 object and a transform server object, which is an object specific to the transformation class (for example, DataPumpTransformCopy or DataPumpTransformDateTimeString). To create both of these, use the New method of the Transformations collection of the DataPumpTask2, DataDrivenQueryTask2, or the TransformationSet object of the ParallelDataPumpTask.

To access the transform server object, use the TransformServer property of Transformation2 object to return a reference to the object. The properties of the transform server object also can be referenced through the TransformServerProperties collection of the generic Transformation2 object.

If the transformation is to support any phase other than the Transform phase, set the TransformPhases property of the Transformation2 object to the sum of the codes from DTSTransformPhaseEnum for the phases it is to support. Add the Transformation2 object to the Transformations collection.

At least one transformation is required for the Transform phase. Transformations for the other phases are optional. When multiple transformations are supplied for a phase, they are all executed at the time appropriate for the phase, in the order the Transformation2 objects were added to the Transformations collection.

For more information about the transformation classes supplied with SQL Server 2000, see Transformation Objects.

For more information about configuring the Transformation2 object and the transform server objects, see DTS Transformations in Visual Basic.