The DTS Data Pump is a multithreaded OLE DB service provider that provides the infrastructure to import, export, and transform data between heterogeneous OLE DB data sources. OLE DB is used because it provides access to the broadest possible range of relational and nonrelational data stores. The DTS Data Pump is a high-speed in-process COM server that moves and transforms OLE DB rowsets from a source to a destination. Two or more connections are required to perform this operation.
The DTS Data Pump provides an extendable COM-based architecture that allows complex data validations and transformations as data moves from source to destination. The DTS Data Pump makes the full power of Microsoft® ActiveX® scripting languages available to the DTS package, allowing complex procedural logic to be expressed as simple, reusable ActiveX scripts. These scripts can validate, convert, or transform column values as they move from the data source, through the DTS Data Pump, to the destination. New values can be easily calculated from one or more columns in the source rowset and source columns with multiple values can be decomposed into multiple destination columns. ActiveX scripts can also invoke and use the services of any COM object that supports automation.
A DTS Data Pump task includes:
A rowset that provides the data input to the transformation process. Examples of source rowset specifications include a table within a data source specified in a connection object, or the result set of a SELECT statement.
A table within a data source specified in a connection object.
The columns of the source rowset are mapped to the columns of the destination. Columns can be mapped in one to one, one to many, many to one, and many to many relationships. Each mapping specifies one of two transformations:
The contents of the source are copied unchanged to the destination.
An ActiveX script is called to manipulate the source data before placing it in the destination
Custom transformations are written in C++ and are used for transformation logic that is too complex to be written in an ActiveX script. For more information, see Creating DTS Custom Transformations.
DTS Data Pump tasks can be complex, with complex mappings between the source and destination columns. Sophisticated ActiveX scripts can be used to perform complex transformations of the data as it is transferred. If the transformation logic cannot be implemented in an ActiveX script, a custom Data Transformation can be built using Microsoft Visual Basic® or C++. For more information, see Creating DTS Custom Transformations.
The processing done by a DTS Data Pump task is:
Note Transformations are executed on a row-by-row basis in the same order they were added. They can be executed by any COM object that supports the IDTSDataPumpTransform interface, allowing complex data validation, scrubbing, and enhancement operations to be written.