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.
Prev. page
[1]
2
next page 
