Creating a User Interface for a Custom DTS Task
Like this article? We recommend
Like this article? We recommend
Like this article? We recommend
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.