DTS Programming

Implementing and Testing a DTS Custom Transformation

To implement and test a Data Transformation Services (DTS) custom transformation, you need to:

Installing SQL Server 2000 Header and Library Files

To install the header and library files, you must do a custom installation of SQL Server 2000 or the SQL Server 2000 client tools on the computer on which you develop the custom transformation.

To install header and library files during a custom installation

  1. In the Setup Type dialog box, click Custom.

  2. In the Select Component dialog box, under Components, select the Development Tools check box.

  3. Under Sub-Components, select Headers and Libraries and Debugger Interface.
Building the Custom Transformation Framework

You can add code to a standard Active Template Library (ATL) template or use the custom transformation template included with SQL Server 2000 to build the transformation framework. For more information, see Building a Custom Transformation from a Standard ATL Template and Using the ATL Custom Transformation Template.

Adding Properties to a Custom Transformation

Your custom transformation may require properties that are not supplied by the custom transformation framework.

To add properties to a custom transformation

  1. In the Workspace window, right-click the interface for your transformation class, and then click Add Property.

  2. In the Add Property to Interface dialog box, enter the name and type, as well as other requested information.

This procedure adds shells for the get_property and put_property functions to your project. You must provide the code to implement them.

Configuring Visual C++ to Build the Project

Before you attempt to compile any of the framework files, you need to configure Visual C++ to look for SQL Server 2000 header and library files.

To configure Visual C++ to build the project

  1. On the Tools menu, click Options.

  2. In the Options dialog box, click the Directories tab.

  3. In the Show directories for list, enter the paths from the following table at the top of the Directories list for each entry.
File type Path
Executable files C:\Program Files\Microsoft SQL Server\80\Tools\Binn
Include Files C:\Program Files\Microsoft SQL Server\80\Tools\DevTools\Include
Library files C:\Program Files\Microsoft SQL Server\80\Tools\DevTools\Lib
Source files C:\Program Files\Microsoft SQL Server\80\Tools\DevTools\Include

This only needs to be done once after installing SQL Server 2000. The paths will be different if SQL Server 2000 components were installed to other than the default locations.

You also need to define the preprocessor symbol _ATL_NO_UUIDOF.

To enter preprocessor symbols

  1. On the Project menu, click Settings.

  2. Click the C/C++ tab, and then in the Preprocessor definitions box, enter the preprocessor symbols (comma separated) at the end of the list.
Adding Code to the Framework

You need to add code for the logic specific to your transformation.

All transformations need IDTSDataPumpTransform::ValidateSchema, plus either IDTSDataPumpTransform::Execute or IDTSDataPumpTransform2::ProcessPhase, in order to be functional beyond a placeholder that returns NOERROR. If the transformation is to run in DTS Designer, you also need to provide IDTSDataPumpTransform2::PreValidateSchema. For more information, see IDTSDataPumpTransform Interface and IDTSDataPumpTransform2 Interface.

For more information about coding transformation logic, see DTS Custom Transformation Example: Copy One Column and DTS Custom Transformation Example: Format Names.

Registering Custom Transformations

When you build the custom transformation project in Visual C++, it registers the transformation as the final step of the build process. If you have enabled DTS component caching, you will need to refresh the cache or DTS Designer will not be able to see the transformation component.

To refresh the cache

  1. In SQL Server Enterprise Manager, right-click Data Transformation Services, and then click Properties.

  2. In the Package Properties dialog box, click Refresh Cache.

If you want to remove a custom transformation from your computer, you must unregister it before deleting the component .dll file.

To unregister a custom transformation

  1. From the command prompt, set the path to the folder that contains the transformation component DLL.

  2. Enter:
       regsvr32 /u Component.dll

  3. If DTS caching is enabled, refresh the cache.
Debugging Custom Transformations

You can debug a custom transformation by running it from DTS Designer or from a DTS package program (for example, one implemented in Microsoft Visual Basic®). Using DTS Designer may give you more testing options, as IDTSDataPumpTransform2::PreValidateSchema is called and a custom user interface can be displayed. In either case, you must specify the executable name and path, along with any parameters the executable requires, on the Debug tab of the Project Settings dialog box.

If you use DTS Designer, you typically enter C:\WINNT\system32\mmc.exe in the Executable for debug session box and /s "c:\Program Files\Microsoft SQL Server\80\Tools\BINN\SQL Server Enterprise Manager.MSC" in the Program arguments box.

To determine the correct debugging settings for your computer

  1. Find the shortcut used to launch SQL Server Enterprise Manager from the Start menu.

  2. Extract this information from the Target box on the Shortcut tab of the Enterprise Manager Properties dialog box.

When a transformation commits an access violation or other fatal error, the data pump terminates it and reports that the task using the transformation failed. For example, when an access violation occurs, the message "Access is denied" is displayed. It is recommended you place a breakpoint at the entry to IDTSDataPumpTransform::OnTransformComplete or the OnPumpComplete code in IDTSDataPumpTransform2::ProcessPhase. If this breakpoint is reached unexpectedly before all rows are processed, a likely cause is a transformation fatal error.