Home > Articles > Data > SQL Server

Creating a User Interface for a Custom DTS Task

  • Print
  • + Share This
Marcelino Santos shows you how to add a user interface when creating a custom DTS task. This article builds on the example of creating a custom DTS task as outlined in the book. The custom task example takes an ADO recordset data previously saved (by an ExecuteSQL task) in a global variable. The custom task then generates an HTML table containing the recordset data and writes the HTML text to a text file. The file can then be easily "included" in a Web page and displayed.
This article is excerpted from SQL Server DTS, by Steve Hughes, Steve Miller, Jim Samuelson, Marcelino Santos, and Brian Sullivan.
From the author of

From the author of

When creating a custom DTS task, a property page is typically used to display all the properties of the task object. The default properties provider in DTS Designer will display a default property page if you do not implement a custom task user interface for your custom task. If you plan to provide a user interface for your custom task, you can create a form that will be the property page for that user interface.

In Visual Basic, choose Add Form from the Project menu to bring up the Add Form dialog box.

Make sure that the icon named Form is selected; then click on the Open button. This will add a regular form named Form1 to your project. Change the name of the form to frmGenHTMLTaskProperties (or whatever form name you prefer). Also, change the form's caption to Generate HTML Task Properties.

With the form selected in the Project window, choose Object from the View menu to display the blank form. Add the form elements in Table 1 to the form.

Table 1 Form Elements for the Property Page

Object

Name

Text/Caption

Style

Frame

fraDescription

Description

 

Frame

fraSource

Source

 

Frame

fraDestination

Destination

 

Textbox

txtDescription

Generate HTML Task: undefined

 

Textbox

txtFilename

 

 

Label

lblGlobalVar

Global Variable:

 

Label

lblFilename

Filename:

 

Combobox

cboGlobalVars

 

2-Drop-down List

DriveListBox

drvDestination

 

 

DirListBox

dirDestination

 

 

FileListBox

filDestination

 

 

CommandButton

cmdCancel

Cancel

 

CommandButton

cmdClose

Close

 


The form needs a TaskObject property that will contain the instance of the task object provided to the form during run time. When this property is set, it needs to do several things:

  • Display the task description in the form.

  • Load the names of global variables into the combo box.

  • Select the current global variable in the combo box (if already set).

  • Set the drive, path, and filename in DriveListBox, DirListBox, and FileListBox.

  • Display the current filename in the form.

Listing 1 shows the code that does all these things.

Listing 1-Initializing a Properties Form for the Custom Task

Private oTask As DTS.Task
Private oCustomTask As clsMyTask

Public Property Set TaskObject (ByVal pTask As DTS.Task)
   Dim oTasks As DTS.Tasks
   Dim oPkg As DTS.Package
   Dim i As Integer
   Dim SaveIndex As Integer

   On Error Resume Next

   If Not pTask Is Nothing Then
    If TypeOf pTask Is DTS.Task Then
     Set oTask = pTask
     Set oCustomTask = pTask.CustomTask

     'Display the custom task's description
     txtDescription.Text = oCustomTask.Description

     'Get a reference to the containing package
     Set oTasks = oTask.Parent
     Set oPkg = oTasks.Parent

     'Load the names of the global variables into the combobox
     For i = 1 To oPkg.GlobalVariables.Count
       'Add only variables of type recordset
       If TypeName(oPkg.GlobalVariables(i).Value) = "Recordset" Then
        cboGlobalVars.AddItem oPkg.GlobalVariables(i).Name
       End If
     Next I

     'Select the current global variable in the combobox
     If oCustomTask.GlobalVarName <> "" Then
       For i = 0 To cboGlobalVars.ListCount - 1
        If cboGlobalVars.List(i) = oCustomTask.GlobalVarName Then
         cboGlobalVars.ListIndex = I
         Exit For
        End If
       Next i
     End If

     'Display the current path and file names
     drvDestination.Drive = Left(oCustomTask.Pathname, 1)
     dirDestination.Path = oCustomTask.Pathname
     filDestination.Filename = oCustomTask.Filename
     txtFilename.Text = oCustomTask.Filename
    Else
     Err.Raise 1027 + vbObjectError, Me.Name, "Parameter pTask is not a Task "
    End If
   Else
    Err.Raise 1027 + vbObjectError, Me.Name, "Parameter pTask is Nothing"
   End If
End Property

When the Cancel button is clicked, the form will have to be unloaded:
Private Sub cmdCancel_Click()
   Unload Me
End Sub

Finally, when the Close button is clicked, the properties have to be updated, and the form has to be unloaded (see Listing 2). 1

Listing 2-Setting the Custom Task's Custom Properties

Private Sub cmdClose_Click()
   If txtDescription.Text = "" Then
    MsgBox "Please enter a description.",, "Description"
    Exit Sub
   Else
    oCustomTask.Description = txtDescription.Text
   End If

   If cboGlobalVars.Text = "" Then
    MsgBox "Please select a global variable.",,"Global Variable"
    Exit Sub
   Else
    oCustomTask.GlobalVarName = cboGlobalVars.Text
   End If

   oCustomTask.Pathname = dirDestination.Path

   If txtFilename.Text = "" Then
    MsgBox "Please enter a filename.",, "Filename"
    Exit Sub
   Else
    oCustomTask.Filename = txtFilename.Text
   End If

   Unload Me
End Sub

About This Article

This article is excerpted from SQL Server DTS by Steve Hughes, Steve Miller, Jim Samuelson, Marcelino Santos, and Brian Sullivan (New Riders Publishing (http://www.newriders.com), 2001, ISBN 0735711232). Refer to Chapter 16, "Creating Your Own Custom Task," for more detailed information on the material covered in this article.

  • + Share This
  • 🔖 Save To Your Account