Microsoft KB Archive/328587

= 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 &quot;Build, Register, and Install the Custom Task&quot; 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

&quot;C:\Program Files\Microsoft SQL Server\80\Tools\Binn\dtspkg.dll&quot;

/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:  _ 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 &quot;Build, Register, and Install the Custom Task&quot; section in this article.  _ 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 &quot;Build, Register, and Install the Custom Task&quot; section in this article:  _ 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:  The plumbing code is now complete.

back to the top

Add functionality to the custom task
The &quot;Build, Register, and Install the Custom Task&quot; 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 &quot;DTS Custom Task Fundamentals&quot; 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: <ul> 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.</li> pPackageEvents: This is a reference to the events of a Package2 object. You can use this object to raise events.</li> 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.</li> pTaskResult: This holds the result of the package execution. You can see the return codes for this parameter in the &quot;DTSTaskExecResult&quot; topic in SQL Server Books Online.</li></ul> </li></ul>

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: <ol> Create the RCW for Dtspkg.dll. This procedure is explained in the &quot;Create a runtime callable wrapper for Dtspkg.dll&quot; section of this article.</li> Install the RCW in the GAC. This procedure is explained in the &quot;Install the RCW in the Global Assembly Cache&quot; section in this article.</li> Create a new Visual Basic .NET Class Library project.</li>  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(&quot;38ED4F80-9EF4-4752-8478-65D2DB3BA7DD&quot;), _ ComVisible(True), _ ProgId(&quot;DTSCustomTaskNet.SimpleCustomTask&quot;)> _ Public Class SimpleCustomTask Implements CustomTask

Private m_name As String Private m_description As String

Public Sub New m_name = &quot;&quot; m_description = &quot;&quot; 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 = &quot;Software\Microsoft\Microsoft SQL Server\80\DTS\Enumeration\Tasks&quot; Const CATID_DTSCustomTask As String = &quot;{10020200-EB1C-11CF-AE6E-00AA004A34D5}&quot;

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

Dim guid As String = &quot;{&quot; + t.GUID.ToString + &quot;}&quot; guid = guid.ToUpper

Console.WriteLine(&quot;RegisterServer {0}&quot;, 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(&quot;CLSID\&quot; & guid & &quot;\Implemented Categories&quot;, 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(&quot;&quot;, 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 = &quot;{&quot; & t.GUID.ToString & &quot;}&quot; guid = guid.ToUpper

Console.WriteLine(&quot;UnregisterServer {0}&quot;, 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(&quot;CLSID\\&quot; + guid + &quot;\\Implemented Categories&quot;, True) rk.DeleteSubKey(CATID_DTSCustomTask, False) rk.Close root.Close

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

root = Registry.ClassesRoot rk = root.OpenSubKey(&quot;CLSID\\&quot; + 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 </li> Add a reference to the RCW that you created in step 1, and then add a reference to System.Windows.Forms.dll.</li> Build the custom task. The output is a .NET assembly.</li> Register and install the custom task assembly: <ol> 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

</li>  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(&quot;<path to CustomTask.snk>&quot;)> </li> 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.</li> 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.</li></ol> </li> 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 &quot;Troubleshooting&quot; section in this article.</li> <li>Run the package.</li></ol>

back to the top

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(&quot;Testing.&quot;) 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 &quot;Test&quot;. 'Global variables MsgBox(&quot;Global variable: &quot; & _        package.GlobalVariables.Item(&quot;Test&quot;).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(&quot;DTSTask_DTSCreateProcessTask_1&quot;) Dim ctsk As CustomTask = tsk.CustomTask ctsk.Properties.Item(&quot;ProcessCommandLine&quot;).Value = &quot;CALC.EXE&quot; 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(&quot;CustomTaskName&quot;) set cus = tsk.CustomTask 'using the Properties collection to change the Description property tsk.Properties(&quot;Description&quot;).Value = &quot;Description of custom task&quot; 'directly referencing the property will also work tsk.Description =&quot;Description of custom task&quot; 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 (&quot;DTSCustomTaskNet.SimpleCustomTask: undefined&quot;) based on the ProgID. It also builds a unique Name (&quot;DTSTask_DTSCustomTaskNet.SimpleCustomTask_1&quot;) 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(&quot;CLSID\\&quot; + guid, True) rk.SetValue(&quot;SimpleCustomTask default description&quot;, _               ctsk.Description) nrk.Close rk.Close root.Close back to the top

<div class="references_section">