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):
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 IsNothing(pPackageLog) And (Marshal.IsComObject(pPackageLog)) Then
Marshal.ReleaseComObject(pPackageLog)
End If
If 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:
- 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:
- 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>")>
- 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. - 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