Implementing and Testing a DTS Custom Task

Implementing and Testing a DTS Custom Task

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

• Install the Microsoft® SQL Server™ 2000 header and library files on your development computer.
  
  
• Build a custom task framework.
  
  
• Configure Microsoft Visual C++® to build the project.
  
  
• Add custom code to the task framework.
  
  
• Register and optionally unregister the custom task.
  
  
• Debug the custom task.

Installing SQL Server 2000 Header and Library Files

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

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, check the Headers and Libraries and Debugger Interface check boxes.

Building a Custom Task Framework

To build the task framework, add code to a standard Active Template Library (ATL) template or use the custom task templates included with SQL Server 2000 . For more information, see Building a Custom Task from the ATL Custom Task Basic Template and Building a Custom Task with a User Interface from the ATL Custom Task Templates.

Configuring Visual C++ to Build the Project

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

To configure Visual C++ to access SQL Server 2000 files

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 custom task.

For all tasks, you need to implement the functionality of your task in the Execute method. Typically, the Name and Description properties do not need to be modified. Logic needs to be added to the Properties property only when the functionality of the DTS default properties provider is insufficient. For more information, see DTS Custom Task Fundamentals.

For tasks supporting a user interface, you need to add logic to the New and Edit methods to display the task user interface. If the task is to display a Help page, you need to add logic to display the page in the Help method. The code generated by the templates for these methods returns E_NOTIMPL, which causes DTS Designer to display the DTS default property grid or a generic Help page. For more information, see Including a DTS Custom Task User Interface.

To add properties and methods to your custom task

1. In the Workspace window, right-click the interface for your custom task class or user interface class, depending on where you want to add the element. Then click Add Property or Add Method.
   
   
2. In the Add Property to Interface or Add Method to Interface dialog box, enter the name, type and other requested information.

   For properties, you will need to specify whether get_property, put_property, or both are supported. Typically, for read/write properties, select the Get Function and Put Function check boxes and click PropPut. For read-only properties, select the Get Function check box.

3. Click Attributes, and then change the helpstring to something meaningful for the property or method.

   If you change the id, make certain you do not cause the elements you add to come before the elements added by the templates in the interface definition lists in the .idl file.

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

The procedure also adds entries to the appropriate interface in the project .idl file. Make sure that entries are added at the end of the list for the interface, because the list order determines the structure of the vtable that Visual C++ uses to navigate to the elements of the interface.

For more information about coding custom task logic, see DTS Example: Adding Properties and Icons in Visual C++ and DTS Example: Including a User Interface in Visual C++.

Registering Custom Tasks

When you build the custom task project in Visual C++, it registers the task 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 custom task 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.

When you remove a custom task from your computer, unregister it before deleting the component .dll file.

To unregister a custom task

1. From the command prompt, set the path to the folder that contains the custom task component DLL.
   
   
2. Enter:
      regsvr32 /u Component.dll
   
   
3. If DTS caching is enabled, refresh the cache.

Do not rely on utilities like Microsoft RegClean to remove registry entries after you have deleted the corresponding registered files. These utilities often only partially remove registry entries.

Debugging Custom Tasks

It is recommended that you compile both Unicode and non-Unicode versions of your component, even in the absence of a requirement to run on both types of systems. Clean compilation of both Unicode and non-Unicode versions helps ensure that conversion functions such as OLE2T have been used properly. Set the compilation mode from the Build/Set Active Configuration menu.

You can debug a custom task by running it from DTS Designer or from a DTS package program (for example, one implemented in Microsoft Visual Basic®). 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, find the shortcut used to launch SQL Server Enterprise Manager from the Start menu. Then extract this information from the Target box on the Shortcut tab of the Enterprise Manager Properties dialog box.

If you get access violations that you cannot trap because they do not occur within your component, verify that in your .idl file all interface elements are present and that all user-defined properties and methods come at the end of the lists generated by the ATL templates. You can double check your .idl file structure by opening a new project in Visual Basic and referencing your component. View your component in Visual Basic Object Browser and verify all the properties and methods appear as expected.