Constructing DTS Custom Tasks

Assemble a task to fit your project's needs

For programmable flexibility and extensibility, Data Transformation Services (DTS) has a lot to offer. From performing a simple one-time data transfer to developing complex workflow-driven packages, DTS gives developers the means to implement solutions that match the complexity of their data transformation projects. In previous articles, we discussed how to use custom packages to leverage the DTS object model. This month, we take that discussion further by examining custom tasks, what they are, and why you might use them. We then detail how to build a custom task and how to use it both graphically and programmatically.

What Is a Task?

Tasks are the components of work that a package performs, or the units of work within a package. A task might include executing a script, spawning an external process, spawning a SQL task, or performing a data transformation.

As you can see in Figure 1, DTS contains eight built-in tasks, and each performs a specialized function. These tasks serve as the building blocks for transformation development. But what happens when the built-in tasks aren't sufficient to meet your project requirements? To answer this question, let's look at how tasks fit into the DTS package object model. Figure 2, page 54, details the major collections of a package and illustrates how the built-in tasks derive from the CustomTask object. All tasks, both built-in and user-created, derive from the CustomTask object. Through the CustomTask object, you can extend DTS by creating new tasks to supplement the eight built-in tasks that ship with SQL Server. COM applications use custom tasks directly in the DTS object interfaces, and once registered, the tasks are accessible through the DTS Package Designer.

Why Custom Tasks?

You might be wondering why you would use custom tasks, when you can implement an ActiveX script in a package and access external COM objects to extend those scripts. As is often the case in computing, you must make this design decision based on your projects' requirements. First, consider how DTS packages accomplish their work. Figure 3 shows the layers of abstraction involved in implementing a package. As we mentioned earlier, all packages perform their work through tasks. Tasks do the work directly or pass the work request to an ActiveX script for processing. The ActiveX script can service the job directly or call an external COM object for completion.

So, although implementing your component's business logic as COM objects offers the highest opportunity for reuse, it also adds extra layers of processing to your package. Using custom tasks to accomplish the same work that you might otherwise assign to an ActiveX script and external COM object can increase a package's execution speed and reduce its complexity. Custom tasks won't necessarily help manipulate the data stream (that's the job of a DTS Data Pump—see "Pump Up the Power of DTS," June 1999, for more), but if you need utility tasks to support your package, a custom task might be the answer.

Building a Task

Consider the following example: You're building a data warehouse, and the source data is on a remote, proprietary system that is accessible only by using FTP. Part of the workflow for your weekly refresh cycle needs to include retrieving the data files from this remote system. To retrieve the files and make the FTP process a step within a package's workflow, you can use Visual Basic (VB) to implement an FTP custom task. Note that you can use any COM-compliant programming language to implement custom tasks.

First, create a new VB project. Add a reference to the DTS Package object model, then add a reference to the Internet control. Create a new ActiveX DLL called FTPCustomTask, and name the class module FTPTask. After the project exists, add the object reference. Screen 1 shows the DTS OLE Automation interface that this example uses and that the Microsoft DTSPackage Object Library provides. Next, you need to add a component to provide FTP functionality to the project. As Screen 2 shows, the component is the Microsoft Internet Transfer Control (ITC—for either VB Profes-sional or Enterprise Edition). Now that you've established the environment, create your FTPTask. We wrote this application as a single VB class module. For illustrative purposes, we have broken the task into separate listings. To recreate the task in your environment, combine Listings 1 through 3 into the class module. Alternatively, you can download the finished VB projects at the link to this article, which is available to all subscribers at http://www.sqlmag.com.

All tasks must support the CustomTask object interface. Figure 4 shows the properties and method of the CustomTask object. By inheriting this interface, a DTS package controls the tasks. VB accomplishes this control through the Implements statement. The FTPCustomTask declarations, which you see in Listing 1, begin by using the Implements statement to inherit the CustomTask class. Below the Implements statement are the declarations of the local implementation of the custom task's Name and Description properties. The final section of declarations defines the properties you need to support your FTP implementation.

Next, you implement all the CustomTask interface's exposed attributes. Listing 2 details these implementations. The Name and Description property implementations are nothing more than standard VB property declarations using the msName and msDescription variables previously declared. In the task's Properties collection, things get interesting. As you might guess, the Properties collection contains Property objects that expose the attributes of the task. For highly specialized processing, developers may opt to implement customized code to support a collection of data. However, rather than forcing developers to add collection-support code, DTS can implement the collection. By leveraging COM, the CustomTask object performs the implementation by internally querying the custom task's interface and, through the DTS PropertyProvider utility, collecting any publicly declared attributes as members of the Task's Properties collection. To take advantage of this built-in functionality, simply set the Properties collection to Nothing and declare the FTP-specific variables as Public. Note that the PropertyProvider utility recognizes only attributes that are declared as Public; attributes declared Public using the VB Property Let and Property Get syntax are not correctly exposed.

Now, you must implement the task's Execute method. The Execute method performs the task's work; therefore, its definition is crucial to the task. However, because a step executes a task's actions, the Execute method is never directly invoked. Listing 3 (which you can download along with Listing 4 at the link to this article at http://www.sqlmag.com) details the task's Execute method. Within the subroutine, declare two variables, oINet and oPackage. The oINet object references the ITC. Use the oPackage object to access any package-level properties—global variables, for example—that you might need. To do so, set oPackage to the parameter pPackage of the Execute method. Using this variable is optional. However, if you use it in your task, don't forget to release the reference before completing your task, or unexpected results might occur. Then set the task's execution status parameter, pTaskResult, to the DTS constant DTSTaskExecResult _Success. If a problem occurs, use your subroutine's error handler to reset the status value to DTSTaskExecResult_Failure.

The next section of the Execute method creates the ITC object and performs the FTP processing. Note that to make the FTP process work, you must supply several values, including the URL, a User Name, a Password, and the Source and Destination file names. (You might need to set the task's FTP Control configuration to support the proxy or firewall your network uses.) You obtain these values at runtime through the task's previously defined Properties collection. After the oINet object is created and initialized, the task performs an FTP GET operation to retrieve the data file. If the transfer is successful, the task's next action is an attempt to delete the source file to prevent accidentally re-importing it.

Set the task's execution status value to signal a failure if the transfer fails. At this point, the FTP work is complete and the FTP QUIT command terminates the connection. Before exiting the subroutine, either successfully or through the error handler, release your object references by setting oINet and oPackage to Nothing. You have now completed the creation of the FTPCustomTask. After compiling, it is ready for use.

Using the FTPCustomTask Graphically

Now that you've created the FTPCustomTask, put it to work. For graphical use, you use the DTS Package Designer to register and access the task. First, create a new package. Select your SQL Server server's Data Transformation Services folder from Enterprise Manager, then right-click and select the New Package menu item. The Package Designer can't access the custom task until you register it on the local machine. To register the task, go to the Task menu of the DTS Package Designer and choose the Register Custom Task menu item. This selection launches the Register Custom Task dialog box, which Screen 3 shows. For this example, name the task FTP Task, and direct the registration utility to the FTPCustomTask DLL in the D:FTPCustomTaskFTPCustomTask.dll directory (this is the same directory where you compiled your task). Also, if you supply an icon for the task, you can identify it here. The Package Designer uses the resulting icon to identify the FTP Task. For this example, use the default icon DTS supplies. After you choose OK in the Register Custom Task dialog box, your new FTP Task is registered and is dynamically added to the Package Designer's Task menu and to the Tasks section of the toolbar, as you see in Screen 4. The task is now ready to use. (Don't forget that if you want to register this task on another machine, you first need to copy and register the ITC in the target environment.)

To use the FTP Task, simply drag it into the Package Designer's work area and set its properties. DTS uses an internal utility to identify FTP properties. Likewise, at package design time, DTS provides a graphical interface for setting those properties. Screen 5 shows the Custom Task Properties dialog box, where you set the properties that control the transfer of the data file. (You need to tailor these values to your FTP environment.) Note that the attributes in this dialog box are the ones you defined while building the task. After you set all the values, the task executes. A warning: During development of this task, we experienced several inconsistencies in testing between Windows 98 and Windows NT workstations. Applying the SQL Server 7.0 Service Pack 1 beta code to each machine seemed to resolve the problem.

Using the FTPCustomTask Programmatically

Another option for using the FTPCustomTask is to access it programmatically through the DTS COM interfaces. Create a new VB project, and add a reference to the DTS Package object model. Create a new Standard EXE called FTPTaskExample, delete the default form, and add a module called FTPMain. After the project exists, add a reference to the Microsoft DTSPackage Object Library, as Screen 1 shows.

Now create a subroutine called Main to contain your package. Listing 4 (at http://www.sqlmag.com) details the code for an example package. We wrote this application as a single VB module. You can use Listing 4, adding the project references on Screen 1, to recreate the example in your environment. The subroutine begins by declaring the DTS objects that you want to use to implement the package. Next, create and initialize a DTS Package object. Like the VB project, this package is named FTPTaskExample.

To create a custom task, use the New method of the package's Tasks collection to create an instance of the FTPCustomTask.FTPTask object. Setting its name and description initializes it. For this example task, use the name FTPTask. As with the Package Designer, before you use the FTP Task, you must initialize its properties. The task exposes these attributes via its Properties collection.

Before you execute your package, you must add a step. Unlike when you use the Package Designer, which implicitly creates and associates steps with tasks, you must explicitly create a step and associate it with the task when you programmatically establish a task, or the task won't execute. To create and initialize your step, invoke the New method of the Steps collection. For this step, use the name Step and associate it with your task by assigning the value FTPTask to the step's TaskName property. Because the threading models supported by VB require all custom tasks written in VB to be executed on the main thread, set the step's property ExecuteInMainthread to True.

After adding the step to the package's Steps collection, you can execute the package. Following package execution, a message box displays, informing you that the task is complete. Finally, clean up your variables by setting all your objects to Nothing. The example application is now ready to compile and execute.

Custom tasks make a powerful addition to your DTS packages. In a future issue, we will examine custom transformations.