DTS Tasks in Visual Basic

DTS Programming

DTS Programming

DTS Tasks in Visual Basic

In Microsoft® Visual Basic®, a Data Transformation Services (DTS) task consists of a generic Task object and a class-specific task object, such as DTSFTPTask, DataPumpTask2 or DynamicPropertiesTask object. For more information about the task classes supplied with Microsoft SQL Server™ 2000, see Task Objects.

The basic steps for adding a DTS task in Visual Basic are as follows:

  1. Declare an object variable of type Task and a class-specific task object variable.

  2. Create the task with the New method of the Tasks collection of the Package2 object. Pass the programmatic identifier (ProgID) of the task class to New as an argument.

  3. Use the CustomTask property of the Task object to return a reference to the class-specific task object.

    Do not declare the class-specific object variable as DTS.CustomTask. If you do, the class-specific properties and methods will not be accessible. However, you can use late binding and declare it as Object.

  4. Assign a unique name to the Name property, either of the Task object or class-specific task object.

    This name also must be assigned to the TaskName property of the Step object. For more information, see DTS Package Workflow in Visual Basic.

  5. Set other class-specific task object properties as necessary.

    Most task classes require a reference to one or more Connection objects. Usually, you do this through a property such as SourceConnectionID of the class-specific task object.

  6. Add the Task object to the package with the Add method of the Tasks collection.
Data Pump Task Example

The following code example shows one way to create a Data Pump task, link to Connection objects, and assign names to the source and destination tables. Use of fully qualified table names, as shown here, makes it unnecessary to set the Catalog property of the corresponding Connection object.

'Declare the generic and class-specific task variables.
Dim objTask       As DTS.Task
Dim objDataPump   As DTS.DataPumpTask2
. . .
'Create the task and then link the task to the connections.
Set objTask = objPackage.Tasks.New("DTSDataPumpTask")
Set objDataPump = objTask.CustomTask
objDataPump.Name = "LowerCaseTask"
With objDataPump
    .SourceConnectionID = 1
    .SourceObjectName = "pubs..authors"
    .DestinationConnectionID = 2
    .DestinationObjectName = "[DTS_UE].[dbo].[AuthNames]"
End With
. . .
objPackage.Tasks.Add objTask
File Transfer Protocol Task Example

The following code example shows one way to create a File Transfer Protocol (FTP) task and assign the files to be copied and the destination directory to the appropriate properties:

Note  The DTSFTPTask object requires that Microsoft Internet Explorer 5 be installed on the computer on which the task is to run. Internet Explorer 5 is supplied with SQL Server 2000, but not with SQL Server 2000 Desktop Engine.

'Declare the generic and FTP task objects.
Dim objTask       As DTS.Task
Dim objFTPTask    As DTSCustTasks.DTSFTPTask

'Create the task. Specify the files, the source, and the destination directories.
Set objTask = objPackage.Tasks.New("DTSFTPTask")
Set objFTPTask = objTask.CustomTask
objFTPTask.Name = "FTPSrcDirTask"
With objFTPTask
    .SourceLocation = DTSFTPSourceLocation_Directory
    .SourceSite = "I:\DTS\TestData"
    .SourceFilename = _
        "'File3.dat';'';'123';'NWProdWiz.XLS';'';'458240';"
    .DestSite = "D:\DTS_UE\Dest"
End With
objPackage.Tasks.Add objTask
 

After the Task object has been added to the Tasks collection, the object variables are no longer needed and can be reused for another task or set to Nothing to release their references.