Building a Custom Transformation from a Standard ATL Template

To build a custom transformation, create a project from a standard Active Template Library (ATL) template, add the interfaces and other elements required by all Data Transformation Services (DTS) transformations, and then add the features of the specific transformation.

This topic explains how to add the elements required by all DTS transformations. You can also use the ATL custom transformation template supplied as a sample with Microsoft® SQL Server™ 2000 to build a custom transformation framework. Even if you plan to use the custom transformation template, it is recommended that you understand the features that were added to create the custom template from the standard object template. For more information, see Building a Custom Transformation from the ATL Custom Transformation Template.

Building a Standard ATL Component

To create a standard ATL component that includes a class for the custom transformation using Microsoft Visual C++® version 6.0, do the following:

To build a standard ATL component

On the File menu, click New, and then click the Projects tab.
Click ATL COM AppWizard, and then enter a project name and location.
For this discussion, assume you entered DTSTrans for the project name.
Click Dynamic Link Library (DLL), click Finish, and in the New Project Information dialog box, click OK.
On the Insert menu, click New ATL Object, click Objects, click Simple Object, and then click Next.
On the Names tab, enter a short name.
For this discussion, assume you entered CustomXFm. The wizard will fill in the other fields. The COM/Type field is the name that will appear in the Create New Transformation dialog box of DTS Designer. You can change it from the default CustomXFm Class.
Click the Attributes tab, and then do the following:
- Under Threading Model, click Both.
- Under Interface, click Dual.
- Under Aggregation, click No.
- Select the Support ISupportErrorInfo check box.

The wizards will create files for the DTSTrans component and the CustomXFm class and save them to the project location folder specified in Step 1.

Adding Custom Transformation Features

After creating a standard ATL component with CustomXFm class files, you need to add custom transformation elements. In this section, all files will have the same names you specified in Step 4 of the previous procedure.

File	Features
CustomXFm.h	Function prototypes and COM map entries for the IDTSDataPumpTransform and IDTSDataPumpTransform2 interfaces
CustomXFm.cpp	Initial function definitions for the IDTSDataPumpTransform and IDTSDataPumpTransform2 interfaces
CustomXFm.rgs	Registry subkeys required for DTS transformations

CustomXFm.h

In this header file, you need to add the following:

Include statements for other header files
An entry to the list of interfaces from which the class inherits
COM Map entries
Function prototypes

Adding Include Statements

Add these header file include statements:

#include <oledb.h>
#include <msdadc.h>    
#include <comdef.h> 
#include "dtspump.h"

immediately preceding the include statement for resource.h:

#include "resource.h"       // main symbols

Adding Interface List Entry

To the list of interfaces from which class CCustomXFm inherits, add this reference to IDTSDataPumpTransform2:

    public IDTSDataPumpTransform2,

immediately preceding:

    public ISupportErrorInfo

Adding COM Map Entries

Add these COM map entries for IDTSDataPumpTransform and IDTSDataPumpTransform2:

    COM_INTERFACE_ENTRY(IDTSDataPumpTransform)      // Must still respond to QI on IDTSDataPumpTransform
    COM_INTERFACE_ENTRY(IDTSDataPumpTransform2)     //  even when IDTSDataPumpTransform2 implemented.

Immediately preceding the COM map entry for IDispatch:

    COM_INTERFACE_ENTRY(IDispatch)

Adding Function Prototypes

You must provide the function prototypes for the IDTSDataPumpTransform and IDTSDataPumpTransform2 interfaces. Immediately after the following lines:

// ICustomXFm
public:

add these lines of code:

    // IDTSDataPumpTransform members
    STDMETHOD(Initialize)(THIS_
            DP_IN LPCOLESTR pwzName,                            // Transform name
            DP_IN VARIANT ServerParameters,                     // Parameters to server for this transform
            DP_OUT LPBYTE *ppvTransformServerData               // Transform server state data.
        );
    STDMETHOD(ValidateSchema)(THIS_
            DP_IN LPBYTE pvTransformServerData,                 // Transform server state data.
            DP_INOUT LPCDTSTransformColumnInfo pSrcColumnInfo,  // Source columns and rowdata
            DP_INOUT LPCDTSTransformColumnInfo pDestColumnInfo, // Dest columns and rowdata
            DP_IN IDTSDataConvert *pIDTSDataConvert,            // Pointer to the data conversion interface
            DP_IN DTSTransformFlags eTransformFlags             // Input Flags for Transformation validation and execution
        );
    STDMETHOD(AddVariable)(THIS_ 
            DP_IN LPBYTE pvTransformServerData,                 // Transform server state data.
            DP_IN LPCOLESTR pwzName,                            // Variable name
            DP_IN BOOL bGlobal,                                 // For ActiveX scripts, indicates whether this variable's
                                                                // methods must be qualified by the object name.
            DP_IN VARIANT Variable                              // Variable value; passed to and updatable by Transform
        );
    STDMETHOD(Execute)(THIS_
            DP_IN LPBYTE pvTransformServerData,                 // Transform server state data.
            DP_IN LPCDTSTransformColumnInfo pSrcColumnInfo,     // Source columns and rowdata
            DP_INOUT LPDTSTransformColumnInfo pDestColumnInfo,  // Dest columns and rowdata
            DP_IN IDTSDataConvert *pIDTSDataConvert,            // Pointer to the data conversion interface
            DP_OUT LPDTSTransformStatus pTransformStatus        // Result of transform
        ) {
            return ProcessPhase(pvTransformServerData
                        , pSrcColumnInfo
                        , pDestColumnInfo
                        , pIDTSDataConvert
                        , NULL
                        , pTransformStatus
                    );
        }
    STDMETHOD(OnRowComplete)(THIS_
            DP_IN LPBYTE pvTransformServerData,                 // Transform server state data.
            DP_INOUT LPDTSTransformColumnInfo pSrcColumnInfo,   // Source columns and rowdata
            DP_INOUT LPDTSTransformColumnInfo pDestColumnInfo,  // Dest columns and rowdata
            DP_IN IDTSDataConvert *pIDTSDataConvert,            // Pointer to the data conversion interface
            DP_IN DTSTransformStatus eTransformStatus,          // Result of Execute()
            DP_IN HRESULT hrInsert                              // Result of IRowsetChange::InsertRow()
        );
    STDMETHOD(OnTransformComplete)(THIS_
            DP_IN LPBYTE pvTransformServerData,                 // Transform server state data.
            DP_INOUT LPDTSTransformColumnInfo pSrcColumnInfo,   // Source columns and rowdata
            DP_INOUT LPDTSTransformColumnInfo pDestColumnInfo,  // Dest columns and rowdata
            DP_IN IDTSDataConvert *pIDTSDataConvert             // Pointer to the data conversion interface
        );
    // IDTSDataPumpTransform2 members
    STDMETHOD(GetTransformServerInfo)(THIS_
            DP_OUT BSTR *pbstrHelpString,                       // Description of the server's implementation
            DP_OUT LPDTSTransformPhaseEnum peSupportedPhases    // Phases supported by this server
        );
    STDMETHOD(PreValidateSchema)(THIS_
            DP_IN LPCDTSTransformColumnMetadata pSrcMetadata,   // May be NULL if not required by Transform Server
            DP_IN LPCDTSTransformColumnMetadata pDestMetadata,  // May be NULL if not required by Transform Server
            DP_IN DTSTransformFlags eTransformFlags,            // Input Flags for Transformation validation and execution
            DP_IN DTSTransformPhaseEnum ePhases                 // Phase(s) for which this Transform is to be called.
        );
    STDMETHOD(SetExtendedInfo)(THIS_
            DP_IN IUnknown *pUnkExtendedInfo                    // Pointer to object supplying extended information.
        ) {
            return NOERROR;
        }
    STDMETHOD(ProcessPhase)(THIS_
            DP_IN LPBYTE pvTransformServerData,                 // Transform server state data.
            DP_IN LPCDTSTransformColumnInfo pSrcColumnInfo,     // Source columns and rowdata
            DP_INOUT LPDTSTransformColumnInfo pDestColumnInfo,  // Dest columns and rowdata
            DP_IN IDTSDataConvert *pIDTSDataConvert,            // Pointer to the data conversion interface
            DP_IN LPCDTSTransformPhaseInfo pPhaseInfo,          // Pointer to phase info structure
            DP_OUT LPDTSTransformStatus peTransformStatus       // Result of transform
        );
    STDMETHOD(SetExecuteThreadComplete)(THIS)
        {
            return NOERROR;
        }\

CustomXFm.cpp

You must provide the initial function definitions for the IDTSDataPumpTransform and IDTSDataPumpTransform2 interfaces.

Adding Initial Function Definitions

CustomXFm.rgs

This file contains the registry script for the transformation class. The lines to be added define the localizable transformation description and the component category for DTS transformations.

Adding Registry Script

Insert these lines immediately following the definition of the VersionIndependentProgID subkey:

To verify that the Implemented Categories globally unique identifier (GUID) is correct, look for it in dtspump.h under the definition for CATID_DTSCustomXform. You can verify that it is among the subkeys of HKEY_CLASSES_ROOT\Component Categories\ in the registry of a computer on which either SQL Server or SQL Server 2000 client tools have been installed.