Building a DTS Custom Transformation

The Data Transformation Services (DTS) data pump, which is the engine for the Transform Data, Data Driven Query and Parallel Data Pump tasks, reads rows from a source connection, transforms the row data as necessary, and writes rows to a destination connection. The data pump uses separate components called transformations to transform the data. The transformation performs specific conversions for which it was designed and that are made necessary by the source and destination column data types. One or more transformations are always required, even when the row data is simply copied.

Several transformations are supplied with Microsoft® SQL Server™ 2000. For more information, see DTS Transformations or Transformation Objects. Custom transformations can also be implemented by users and third-party vendors. To implement a custom transformation, you must:

• Use Microsoft Visual C++®. The DTS data pump does not support the interfaces necessary to use components implemented in Microsoft Visual Basic®. Most of the constants, structures and interfaces you will need are defined only in Visual C++ header (.h) files that are supplied with SQL Server 2000 and Visual C++.
  
  
• Implement the IDTSDataPumpTransform interface. If the transformation is to be used with SQL Server 2000, you must also implement the IDTSDataPumpTransform2 interface. Other optional custom transformation interfaces can be implemented as well.
  
  
• Implement the API functions that COM dynamic-link libraries (DLLs) require. You must modify the registration functions, or a registration script, to add and remove the component category globally unique identifier (GUID) for DTS transformations from the system registry.

This section explains how to implement custom transformations and provides examples.