Building a Custom Transformation from a Standard ATL Template

DTS Programming

DTS Programming

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

  1. On the File menu, click New, and then click the Projects tab.

  2. Click ATL COM AppWizard, and then enter a project name and location.

    For this discussion, assume you entered DTSTrans for the project name.

  3. Click Dynamic Link Library (DLL), click Finish, and in the New Project Information dialog box, click OK.

  4. On the Insert menu, click New ATL Object, click Objects, click Simple Object, and then click Next.

  5. 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.

  6. 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

Add these lines of code at the end of the existing file:

// IDTSDataPumpTransform members
STDMETHODIMP CCustomXFm::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.
        )
{
    return NOERROR;
}
STDMETHODIMP CCustomXFm::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
        )
{
    return NOERROR;
}
STDMETHODIMP CCustomXFm::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
        )
{
    return NOERROR;
}
STDMETHODIMP CCustomXFm::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()
        )
{
    return NOERROR;
}
STDMETHODIMP CCustomXFm::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
        )
{
    return NOERROR;
}
// IDTSDataPumpTransform2 members
STDMETHODIMP CCustomXFm::GetTransformServerInfo(THIS_
            DP_OUT BSTR *pbstrHelpString,                       // Description of the server's implementation
            DP_OUT LPDTSTransformPhaseEnum peSupportedPhases    // Phases supported by this server
        )
{
    BSTR bstrHelp = _bstr_t("Helpstring for Custom Transformation Framework");

    // If help string pointer valid, define help string.
    if (pbstrHelpString) 
        *pbstrHelpString = bstrHelp;

    // If supported phases pointer valid, define supported phases
    if (peSupportedPhases) 
        *peSupportedPhases = DTSTransformPhase_Transform;

    return NOERROR;
}
STDMETHODIMP CCustomXFm::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.
        )
{
    return NOERROR;
}
STDMETHODIMP CCustomXFm::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 pTransformStatus        // Result of transform
    )
{
    return NOERROR;
}
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:

            DTSTransform
            {
                '1033'
                {
                    val DTSTransformDescription = s 'Custom Transformation Framework'
                }
            }
            'Implemented Categories'
            {
                {10010100-740B-11D0-AE7B-00AA004A34D5}
            }
            InprocHandler32 = s 'ole32.dll'

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.