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:\FTPCustomTask\FTPCustomTask.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.
End of Article
Prev. page
1
[2]
next page -->
