Multiphase Data Pump Functionality
Data Transformation Services (DTS) provides advanced users the capability to add programs that customize the data pump at various phases of its operation. By customizing the operation of the data pump, you can add a wide range of functionality to a package. For example:
- Row-level restartability, or the ability to restart the data pump without having to reload large numbers of rows that were already processed. You can add functions to save processed row data, batches, or partial batches, writing that data back to the source or a status table for later use.
- Individual handling of types of insert or transformation errors. For example, you could add special error handlers to customize problems handling NULL data or constraint violations.
- Customizing data pump initialization or termination steps. For example, on data pump initialization you could write out a schema header to a file prior to writing XML (Extensible Markup Language) data to the file.
Data Pump Process
The multiphase data pump option allows you to access the data pump at several points during its operation and add functionality. When copying a row of data from source to a destination, the data pump follows this basic process:
- Fetches a row of source data.
- Optionally applies transformations to the row.
- Attempts to insert the row of data to the destination buffer.
- Processes exceptions.
- Stores the results in a batch, if a batch is specified.
- Repeats the previous steps until the batch is filled, then commits the data stored in the buffer and starts the next batch, or rolls back the batch.
After the data pump processes the last row of data, the task is finished and the data pump operation terminates.
Data Pump Phases
The following figure shows the data pump phases and how they map to the data flow.
In the figure, the data flow (detailed in the expanded, gray area of the figure):
- Originates in the Row Transform phase, where a row of data is copied from the source and any transformations are applied.
- If the transformation is successful, the data for the row moves to the next phase. If the transformation is unsuccessful, a Transform Failure occurs, and the next row of data is fetched.
- Moves to the Post Row phase, where an attempt is made to copy the row to the destination buffer. There are two possible outcomes for this operation: Insert Success or Insert Failure.
- Ends in the Batch Complete phase, where the row data is stored in a batch and eventually inserted or not committed, depending on whether a batch was configured for the task, and the size of the batch.
Three additional data pump phases not directly tied to the processing of row data are shown in the figure: Pre Source, Post Source, and Pump Complete. Each of these phases covers events prior to or after the row-by-row copying of data, transformation, and commit (or rollback) process. For example, the Pre Source phase occurs before the first row of data is fetched. The Post Source phase occurs after the last row of data is processed, and the Pump Complete phase occurs at the end of the transformation task.
Accessing Data Pump Phases
To display the multiphase data pump options in DTS Designer, you must select an option in SQL Server Enterprise Manager. After you have selected the multiphase data pump option, the feature will remain accessible to any packages opened in DTS Designer, for any future sessions. By default, this option is not selected.
To activate the multiphase data pump feature
Configuring Data Pump Phases
By default, the data pump is set to operate so that only its Row Transform phase is available. That phase is what you configure when mapping column-level transformations in the Transform Data task, Data Driven Query task, and Parallel Data Pump task, without selecting a phase. Advanced users who want to add functionality to a package so that it supports any data pump phase can do so by:
- Writing a Microsoft® ActiveX® script phase function for each data pump phase to be customized. If you use ActiveX script functions to customize data pump phases, no additional code outside of the package is required.
For more information, see DTSTransformScriptProperties2 Object and Phased Transformation Samples.
To add a multiphase data pump transformation function using an ActiveX script
- Creating a COM object in Microsoft Visual C++® to customize selected data pump phases. You develop this program external to the package, and the program is called for each selected phase of the transformation. Unlike the ActiveX script method of accessing data pump phases, which uses a different function and entry point for each selected phase, this method provides a single entry point that is called by multiple data pump phases, while the data pump task executes.
Note You cannot create a COM object in Microsoft Visual Basic® to customize data pump phases.
To call a COM object that customizes one or more data pump phases
Data Pump Phases
The following sections provide information on the data pump phases you can customize, either through ActiveX scripts or through a custom COM object.
Note When writing multiple functions that access the same data pump phase, the return value from the last function is the one used. To preserve a return value from a prior phase function, you must return the value from the subsequent phase function.
Pre Source Phase
The pre source phase is executed before the first fetch of source data. The phase is executed one time for the entire data pump operation unless you use the DTSTransformStat_SkipFetch return code in your phase function, which creates a loop.
You can add a pre-source data pump function for a transformation to write header rows containing meta data information to a file, and initialize objects, connections, and memory for use in later data pump phases.
Row Transform Phase
This is the default data pump phase available through the Transformations mapping tab of the Transform Data task or the Data Driven Query task. The Main function placeholder supplied on the ActiveX Script Transformation Properties dialog box is the default entry point for adding custom scripting code for this phase. The Row Transform phase allows read access to source data and meta data and write access to destination data.
Post Row Transform Phase
The Post Row transform phase is executed after the row transform phase of the data pump and consists of the Transform Failure, Insert Success, and Insert Failure subphases. Of the subphases, listed below, Insert Success and Insert Failure are mutually exclusive; only one of those subphases can occur for a given row:
- On Transform Failure. This subphase signifies the failure of the usual transform phase (when the row transformation returns DTSTransformStat_Error or DTSTransformStat_ExceptionRow). You can access this subphase in order to handle transformation errors (for example, type mismatches), thus overriding the value returned by the transformation and continuing with execution. After a transform failure occurs, processing for that row may or may not continue. If processing continues, only one of the following subphases can occur.
- On Insert Success. This subphase signifies the success of an Insert operation (or Insert query if the transformation is part of a Data Driven Query task). You cannot specify any destination operations in the returned status.
- On Insert Failure. This subphase signifies the failure of an Insert operation (or Insert query if the transformation is part of a Data Driven Query task). You cannot specify any destination operations in the returned status.
On Batch Complete
You can call this data pump phase on success or failure of a batch or rows, as defined by the value specified in Insert batch size in the Options tab of the Transform Data task. Setting a batch size for a Data Driven Query task or parallel data pump task can only be done programmatically; if you want to write an On batch complete function for either of those tasks, you must do so programmatically.
On Pump Complete
You can customize this phase at the end of the transformation task (after all rows have been processed). Use functions written to On pump complete to free up resources and commit data held in global variables throughout the lifetime of data pump. You cannot access the data through an On pump complete function.
Post Source Data
Access this phase to process the destination data after completion of the task. Unlike On pump complete functions, functions written to this phase allow you to access the destination data. Common uses of a post source data function include writing footer rows to a file, freeing up resources, and committing data held in global variables.