Knowledge Base

How to create a DTS custom task by using Visual Basic .NET

Article ID: 328587

Article Last Modified on 12/7/2007

APPLIES TO

Microsoft SQL Server 2000 Standard Edition
Microsoft SQL Server 2000 64-bit Edition
Microsoft Visual Basic .NET 2002 Standard Edition
Microsoft Visual Basic .NET 2003 Standard Edition

This article was previously published under Q328587

For a Microsoft Visual C# .NET version of this article, see 326909.

IN THIS TASK

SUMMARY Create a runtime callable wrapper for Dtspkg.dll Install the RCW in the Global Assembly Cache Write the plumbing code for the custom task Add functionality to the custom task Build, register, and install the custom task Additional features

Removal of a custom task
Package log support
Global variables
Change the properties of other tasks
Change custom task properties by using an ActiveX script task
Implement package events

Troubleshooting REFERENCES

SUMMARY

This article explains how to create a Data Transformation Services (DTS) Custom Task by using Microsoft Visual Basic .NET.

You can extend the DTS capabilities by creating custom tasks by using Visual Basic .NET. After you install and register the custom task, it appears in DTS Designer like the default DTS tasks. Additionally, you can use the whole .NET Framework to create DTS custom tasks.

In addition to the steps to create a DTS custom task, this article also includes the following information:

Sample code for the custom task, which is in the "Build, Register, and Install the Custom Task" section in this article.
A description of additional features that you can implement by using a custom task.
References to several tools that you can use during the development process. (Unless otherwise noted, these tools are included with Visual Studio .NET, and you run them from the Visual Studio .NET command prompt.)

back to the top

Create a runtime callable wrapper for Dtspkg.dll

If a Microsoft Windows .NET-based client computer accesses a COM component, you must use a wrapper around unmanaged code (which the COM component contains). This type of wrapper is a runtime callable wrapper (RCW) and you build them from the type library information that the Dtspkg.dll exposes. You can use the Type Library Importer tool (Tlbimp. exe) to build the RCW:

tlbimp.exe
"C:\Program Files\Microsoft SQL Server\80\Tools\Binn\dtspkg.dll"
/out:Microsoft.SQLServer.DTSPkg80.dll
/keyfile:DTSPkg.snk

The /keyfile parameter signs Microsoft.SQLServer.DTSPkg80.dll with a strong name by using the public or private key pair in DTSPkg.snk. To create the key pair that is present in DTSPkg.snk, use the Strong Name Tool (Sn.exe):

sn.exe -k DTSPkg.snk

Because you must install the RCW in the global assembly cache (GAC), you must use a strong name like the other assemblies in the GAC.

back to the top

Install the RCW in the Global Assembly Cache

Install the RCW in the GAC by using the Global Assembly Cache Tool (GACutil.exe):

gacutil.exe /i Microsoft.SQLServer.DTSPkg80.dll

After you install the RCW, you can add it as a reference to a Visual Studio .NET C# Class Library project.

After you install the RCW, you can add it as a reference to a Visual Studio .NET Visual Basic Class Library project. However, you must locate the RCW; the installation of an assembly in the GAC does not automatically add it to the list of available references.

back to the top

Write the plumbing code for the custom task

The plumbing code primarily handles the registration of the custom task. The .NET assemblies do not expose DllRegisterServer and DllUnregisterServer entry points like COM components do, but you can use the ComRegisterFunctionAttribute class to perform task registration and unregistration. This class is a part of the System.Runtime.InteropServices namespace.

First, you must specify a GUID and a programmatic identifier (ProgID) for the custom task class. You can use GUIDgen.exe to create a GUID. Add the following code at the line before the declaration of the custom task class:

<Guid("38ED4F80-9EF4-4752-8478-65D2DB3BA7DD"), _
ComVisible(True), _
ProgId("DTSCustomTaskNet.SimpleCustomTask")> _
Public Class SimpleCustomTask
    Implements CustomTask
    'implementation of custom task

The ProgID is made up of the Root Namespace (typically the project name, which is DTSCustomTaskNet in this example) followed by the class name (SimpleCustomTask).

The following code demonstrates the implementation of a function to perform task registration. The complete code for the function is presented in the "Build, Register, and Install the Custom Task" section in this article.

    <System.Runtime.InteropServices.ComRegisterFunctionAttribute()> _
    Shared Sub RegisterServer(ByVal t As Type)
    'code to register custom task

The registration function adds the following subkey to the registry:

HKEY_CLASSES_ROOT\CLSID\38ED4F80-9EF4-4752-8478-65D2DB3BA7DD\Implemented Categories\{10020200-EB1C-11CF-AE6E-00AA004A34D5}

The GUID 10020200-EB1C-11CF-AE6E-00AA004A34D5 is the class ID (CLSID) of the DTS package object. You must do this because all custom tasks implement the CustomTask interface. The registration function then adds the following subkey to the registry:

HKEY_CURRENT_USER\Software\Microsoft\Microsoft SQL Server\80\DTS\Enumeration\Tasks\38ED4F80-9EF4-4752-8478-65D2DB3BA7DD

The following subkey lists the contents of the DTS Task Cache, which causes the custom task to appear in DTS Designer:

HKEY_CURRENT_USER\Software\Microsoft\Microsoft SQL Server\80\DTS\Enumeration\Tasks\

The following code demonstrates the implementation of the unregister function to perform task removal. To view the complete code sample for this function, see the "Build, Register, and Install the Custom Task" section in this article:

    <System.Runtime.InteropServices.ComUnregisterFunctionAttribute()> _
    Shared Sub UnregisterServer(ByVal t As Type)
    'code to unregister custom task

The unregistration function removes the task from the DTS Task Cache by deleting the following subkey from the registry:

HKEY_CURRENT_USER\Software\Microsoft\Microsoft SQL Server\80\DTS\Enumeration\Tasks\38ED4F80-9EF4-4752-8478-65D2DB3BA7DD

Finally, the custom task is exposed as a dual-interface COM component. You create a default interface from all public, non-static fields, properties, and methods of the class. Add the following line of code after the IMPORTS statements in the custom task source file:

<Assembly: ClassInterface(ClassInterfaceType.AutoDual)>

The plumbing code is now complete.

back to the top

Add functionality to the custom task

The "Build, Register, and Install the Custom Task" section of the article contains code for a simple DTS custom task. The task has two properties: Name and Description. When you run the task, the value for the Description property appears in a message box. This example represents the minimum code that you must use to have a functional DTS custom task. However, you can create a custom user interface by implementing the CustomTaskUI interface, but that is beyond the scope of this article. By implementing only the CustomTask interface, DTS Designer creates a default user interface for the custom task.

All DTS custom tasks implement the CustomTask interface. The CustomTask interface is made up of two properties, one collection, and one method:

The Name and Description properties.
The Properties collection.
The Execute method.

All custom tasks must implement the properties, the Properties collection, and the Execute method.

The Name property identifies the task in the DTS package. When you put the task on the DTS Designer surface, DTS Designer assigns a name to the task. For the sample custom task, you can modify the Name property. This is for illustration purposes only as SQL Server Books Online recommends that you do not expose the Name property to the user because this disassociates the task from its step. The following excerpt is from the "DTS Custom Task Fundamentals" topic in SQL Server Books Online:

It is recommended that you do not expose the Name property, especially in a read/write mode. DTS Designer assigns a unique name to the task when the task icon is placed on the design sheet. If you change the value of Name, DTS Designer will look for the task using the old name and fail when it cannot find it.

To work around this, use the Disconnected Edit feature in DTS Designer to change the TaskName property of the step that belongs to the task to reflect the new name.
With the Description property, you can add a meaningful description to the task.
The Properties collection contains Property objects that identify the properties of the custom task. The properties grid for the task appears in DTS Designer, and it obtains its information from the Properties collection. By setting this to return nothing, the default DTS properties provider is used.
The Execute method includes the functionality of the custom task. In the example, the Execute method displays the value of the Description property of the task in a message box. The Execute method has the following four parameters:
- pPackage: This is a reference to the parent DTS Package2 object. You can use this object to get references to other objects in the package and to modify their properties.
- pPackageEvents: This is a reference to the events of a Package2 object. You can use this object to raise events.
- pPackageLog: This is a reference to a PackageLog object that represents the package log file. You can use this object to write to the log if you have enabled DTS package logging.
- pTaskResult: This holds the result of the package execution. You can see the return codes for this parameter in the "DTSTaskExecResult" topic in SQL Server Books Online.

back to the top

Build, register, and install the custom task

The following procedure contains the code for the custom task sample that is mentioned in the article. To build and to run the custom task, follow these steps:

Create the RCW for Dtspkg.dll. This procedure is explained in the "Create a runtime callable wrapper for Dtspkg.dll" section of this article.
Install the RCW in the GAC. This procedure is explained in the "Install the RCW in the Global Assembly Cache" section in this article.
Create a new Visual Basic .NET Class Library project.

Add the following code to the class file. Use the GUID value by using GUIDgen.exe, and then paste it in the source file:

Imports System.Runtime.InteropServices
Imports Microsoft.SQLServer.DTSPkg80
Imports Microsoft.Win32
Imports System.Windows.Forms

<Assembly: ClassInterface(ClassInterfaceType.AutoDual)> 

<Guid("38ED4F80-9EF4-4752-8478-65D2DB3BA7DD"), _
ComVisible(True), _
ProgId("DTSCustomTaskNet.SimpleCustomTask")> _
Public Class SimpleCustomTask
    Implements CustomTask

    Private m_name As String
    Private m_description As String

    Public Sub New()
        m_name = ""
        m_description = ""
    End Sub

    Public Overloads Sub Execute(ByVal pPackage As Object, _
        ByVal pPackageEvents As Object, ByVal pPackageLog As Object, _
        ByRef pTaskResult As Microsoft.SQLServer.DTSPkg80.DTSTaskExecResult) _
        Implements CustomTask.Execute

        Try
            DoExecute(pPackage, pPackageEvents, pPackageLog, pTaskResult)
        Finally
            If NOT IsNothing(pPackageLog) And (Marshal.IsComObject(pPackageLog)) Then
                Marshal.ReleaseComObject(pPackageLog)
            End If
            If NOT IsNothing(pPackageEvents) And (Marshal.IsComObject(pPackageEvents)) Then
                Marshal.ReleaseComObject(pPackageEvents)
            End If
        End Try

    End Sub

    Public Overloads Sub DoExecute(ByVal pPackage As Object, _
        ByVal pPackageEvents As Object, ByVal pPackageLog As Object, _
        ByRef pTaskResult As Microsoft.SQLServer.DTSPkg80.DTSTaskExecResult)

        'Assume failure at the outset
        pTaskResult = DTSTaskExecResult.DTSTaskExecResult_Failure

        Try
            Dim package As Package2 = CType(pPackage, Package2)
            Dim packageEvents As PackageEvents = CType(pPackageEvents, PackageEvents)
            Dim packageLog As PackageLog = CType(pPackageLog, PackageLog)

            MessageBox.Show(Description)
        Catch e As System.Runtime.InteropServices.COMException
            Console.WriteLine(e)
        Catch e As System.Exception
            Console.WriteLine(e)

        End Try

        'Return success
        pTaskResult = DTSTaskExecResult.DTSTaskExecResult_Success

    End Sub

    Public Overloads Property Description() As String _
        Implements CustomTask.Description

        Get
            Return Me.m_description
        End Get

        Set(ByVal Value As String)
            Me.m_description = Value
        End Set

    End Property

    Public Overloads Property Name() As String Implements CustomTask.Name

        Get
            Return m_name
        End Get

        Set(ByVal Value As String)
            Me.m_name = Value
        End Set

    End Property


    Public Overloads ReadOnly Property Properties() _
        As Microsoft.SQLServer.DTSPkg80.Properties _
        Implements CustomTask.Properties

        Get
            Return Nothing
        End Get

    End Property

    <System.Runtime.InteropServices.ComVisible(False)> _
    Public Overrides Function ToString() As String

        Return MyBase.ToString()

    End Function

    Const TASK_CACHE As String = "Software\Microsoft\Microsoft SQL Server\80\DTS\Enumeration\Tasks"
    Const CATID_DTSCustomTask As String = "{10020200-EB1C-11CF-AE6E-00AA004A34D5}"

    'Registration function for custom task
    <System.Runtime.InteropServices.ComRegisterFunctionAttribute()> _
    Shared Sub RegisterServer(ByVal t As Type)
        Try

            Dim guid As String = "{" + t.GUID.ToString() + "}"
            guid = guid.ToUpper()

            Console.WriteLine("RegisterServer {0}", guid)

            Dim root As RegistryKey
            Dim rk As RegistryKey
            Dim nrk As RegistryKey

            ' add COM Category in HKEY_CLASSES_ROOT
            root = Registry.ClassesRoot
            rk = root.OpenSubKey("CLSID\" & guid & "\Implemented Categories", True)
            nrk = rk.CreateSubKey(CATID_DTSCustomTask)
            nrk.Close()
            rk.Close()
            root.Close()

            ' add to DTS Cache in HKEY_CURRENT_USER
            root = Registry.CurrentUser
            rk = root.OpenSubKey(TASK_CACHE, True)
            nrk = rk.CreateSubKey(guid)
            nrk.SetValue("", t.FullName)
            nrk.Close()
            rk.Close()
            root.Close()
        Catch e As Exception
            System.Console.WriteLine(e.ToString())
        End Try
    End Sub 'RegisterServer


    'Unregistration function for custom task
    <System.Runtime.InteropServices.ComUnregisterFunctionAttribute()> _
    Shared Sub UnregisterServer(ByVal t As Type)
        Try

            Dim guid As String = "{" & t.GUID.ToString() & "}"
            guid = guid.ToUpper()

            Console.WriteLine("UnregisterServer {0}", guid)

            Dim root As RegistryKey
            Dim rk As RegistryKey

            ' delete from DTS Cache in HKEY_CURRENT_USER
            root = Registry.CurrentUser
            rk = root.OpenSubKey(TASK_CACHE, True)
            rk.DeleteSubKey(guid, False)
            rk.Close()
            root.Close()

            'Delete task related keys from HKEY_CLASSES_ROOT

            root = Registry.ClassesRoot
            rk = root.OpenSubKey("CLSID\\" + guid + "\\Implemented Categories", True)
            rk.DeleteSubKey(CATID_DTSCustomTask, False)
            rk.Close()
            root.Close()


            root = Registry.ClassesRoot
            rk = root.OpenSubKey("CLSID\\" + guid, True)
            rk.DeleteValue("DTSTaskDescription")
            rk.Close()
            root.Close()

            root = Registry.ClassesRoot
            rk = root.OpenSubKey("CLSID\\" + guid, True)
            rk.DeleteSubKey(guid, False)
            rk.Close()
            root.Close()
            '

        Catch e As Exception

            System.Console.WriteLine(e.ToString())

        End Try

    End Sub

End Class

Add a reference to the RCW that you created in step 1, and then add a reference to System.Windows.Forms.dll.
Build the custom task. The output is a .NET assembly.
Register and install the custom task assembly:
1. Because you must add the custom task to the GAC, you must use a strong name. Like the RCW, you can create a strong name file by using the Strong Name tool:
  
  sn.exe -k CustomTask.snk
2. To build the custom task assembly with the strong name, edit or add the following line of code in the AssemblyInfo.vb file:
```
<assembly: AssemblyKeyFile("<path to CustomTask.snk>")>
                        
```
3. Now build the task by using Visual Studio .NET. The output is a .NET assembly:
  
  regasm.exe CustomTask.dll
  
  NOTE: By running the Registry Assembly Tool (Regasm.exe), required registry entries are added that allow DTS to transparently create the .NET custom task class.
4. The last step in the development of a DTS custom task is to install it in the GAC by using the Global Assembly Cache Tool (GACutil.exe):
  
  gacutil.exe /i CustomTask.dll
  
  To use the custom task, open DTS Designer in SQL Server Enterprise Manager, and then put the custom task on the design surface. In the properties of the custom task, set the Description property, and then run the package. The result is a message box that displays the value for the Description property of the task.
Create a new DTS Package: In the DTS Designer in SQL Server Enterprise Manager, put the task on the design surface, open the properties of the task, and then set the Description property. For additional information, see the "Troubleshooting" section in this article.
Run the package.

back to the top

Additional features

Removal of a custom task

To remove a custom task, remove it from the GAC, and then use the Registry Assembly Tool to remove the appropriate registry entries: gacutil.exe /u CustomTask
regasm.exe /u CustomTask.dll

GACutil.exe expects the name of the assembly as it appears in the cache and not the actual file name. To verify this name, start Windows Explorer, and then locate the %SystemRoot%\Assembly folder.

back to the top

Package log support

The Execute method contains the pPackageLog parameter which is a reference to the package log file. To write to a user-defined message to the log file, call PackageLog.WriteStringToLog in the Execute method. (You can also use the WriteTaskRecord method; for additional information, see SQL Server Books Online.)

Make sure that you enable DTS logging in the package properties. After you enable logging, use the code to verify that logging is enabled before you actually write to the log:

    'Logging
    If Not pPackageLog Is Nothing Then
        CType(pPackageLog, PackageLog).WriteStringToLog("Testing.")
    End If

back to the top

Global variables

You can use the reference to the Package2 object in the Execute method to access global variables. The following example uses a global variable named "Test".

    'Global variables
    MsgBox("Global variable: " & _ 
        package.GlobalVariables.Item("Test").Value)

back to the top

Change properties of other tasks

You can use the reference to the Package2 object in the Execute method to change the properties of other DTS tasks. To test the following sample code, add an Execute Process task to your package, specify Notepad.exe as the process to start, and then set it to run when the custom task is complete. This sample code causes the task to start the Windows Calculator (Calc.exe) instead of Notepad:

    'Change properties of another task
    Dim tsk As Task = package.Tasks.Item("DTSTask_DTSCreateProcessTask_1")
    Dim ctsk As CustomTask = tsk.CustomTask
    ctsk.Properties.Item("ProcessCommandLine").Value = "CALC.EXE"

back to the top

Change custom task properties by using an ActiveX script task

Like the default DTS tasks, you can change the properties of a custom task by using an ActiveX Script task. The following sample Visual Basic script demonstrates two ways to change the Description property of the custom task:

Function Main()
    set pkg = DTSGlobalVariables.Parent
    set tsk = pkg.Tasks("CustomTaskName")
    set cus = tsk.CustomTask
    'using the Properties collection to change the Description property
    tsk.Properties("Description").Value = "Description of custom task"
    'directly referencing the property will also work
    tsk.Description ="Description of custom task"
    Main = DTSTaskExecResult_Success
End Function

back to the top

Implement package events

For more information about how to handle DTS package events, click the following article number to view the article in the Microsoft Knowledge Base:

321525 How to use DTS package events in Visual Basic .NET

back to the top

Troubleshooting

If you specify a default Description property for the custom task in its constructor, this property is ignored when you drag the task in DTS Designer. DTS Designer tries to read this value from the registry; if it is not found, it builds a default Description ("DTSCustomTaskNet.SimpleCustomTask: undefined") based on the ProgID. It also builds a unique Name ("DTSTask_DTSCustomTaskNet.SimpleCustomTask_1") based on the ProgID.

To specify a default Description property for the custom task, insert the value that you want in the registry by adding the following code to the RegisterServer subroutine in the sample project:

            ' add default Description in HKEY_CLASSES_ROOT
            Dim ctsk As DTSCustomTaskNet.SimpleCustomTask = _
                New DTSCustomTaskNet.SimpleCustomTask()
            root = Registry.ClassesRoot
            rk = root.OpenSubKey("CLSID\\" + guid, True)
            rk.SetValue("SimpleCustomTask default description", _
                ctsk.Description)
            nrk.Close()
            rk.Close()
            root.Close()

back to the top

REFERENCES

For additional information about how to develop custom tasks for SQL Server 2000, see the "Building a DTS Custom Task" topic in SQL Server Books Online:

http://msdn2.microsoft.com/en-us/library/aa936761.aspx

back to the top

Keywords: kbhowtomaster kbinterop kbwindowsforms KB328587

Microsoft KB Archive/328587

Contents

How to create a DTS custom task by using Visual Basic .NET

IN THIS TASK

SUMMARY

Create a runtime callable wrapper for Dtspkg.dll

Install the RCW in the Global Assembly Cache

Write the plumbing code for the custom task

Add functionality to the custom task

Build, register, and install the custom task

Additional features

Removal of a custom task

Package log support

Global variables

Change properties of other tasks

Change custom task properties by using an ActiveX script task

Implement package events

Troubleshooting

REFERENCES