How to create a Data Transformation Services custom task by using Visual C# .NET (326909)


The information in this article applies to:

  • Microsoft SQL Server 2000 64 bit (all editions)
  • Microsoft SQL Server 2000 (all editions)
  • Microsoft Visual C# .NET (2002)
This article was previously published under Q326909

IN THIS TASK

SUMMARY

This article describes how to create a Data Transformation Services (DTS) custom task by using Microsoft Visual C# .NET.

You can extend the DTS capabilities by creating custom tasks by using Visual C# .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.

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("A39847F3-5845-4459-A25E-DE73A8E3CD48"), ComVisible(true)]
[ProgId("DTS.SimpleTask")]

public class SimpleTask : CustomTask
{
	//implementation of custom task
}
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()]
static void RegisterServer(Type t)
{
	//code to register custom task 
}
The registration function adds the following subkey to the registry:

HKEY_CLASSES_ROOT\CLSID\A39847F3-5845-4459-A25E-DE73A8E3CD48\Implemented Categories\{10020200-EB1C-11CF-AE6E-00AA004A34D5}

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\A39847F3-5845-4459-A25E-DE73A8E3CD48

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. The unregister function is part of the ComUnregisterFunctionAttribute class in the .NET Framework Class Library. 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()]
static void UnregisterServer(Type t)
{
	//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\A39847F3-5845-4459-A25E-DE73A8E3CD48

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 USING 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.

Name property:

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 you put the task icon 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, change the TaskName property of the step that belongs to the task to reflect the new name by using the Disconnected Edit feature in DTS Designer.

Description property:

With the Description property, you can add a meaningful description to the task.

Properties collection:

The Properties collection contains Property objects that identify the properties of the custom task. By setting this to return null, the default DTS properties provider is used. The properties grid for the task appears in DTS Designer, and it obtains its information from the Properties collection.

Execute method:

The Execute method contains the functionality of the custom task. In the Description 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

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

To build the custom task assembly with the strong name, edit the following line in the AssemblyInfo.cs 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. 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.

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:
  1. Create the RCW for Dtspkg.dll. This procedure is explained in the "Create a Runtime Callable Wrapper for Dtspkg.dll" section of this article.
  2. Install the RCW in the GAC. This procedure is explained in the "Install the RCW in the Global Assembly Cache" section in this article.
  3. Create a new Visual C# .NET Class Library project.
  4. Add the following code to the class file. Use the GUID value by using GUIDgen.exe, and then paste it in the source file:
    
using System;
using System.Runtime.InteropServices;
using Microsoft.SQLServer.DTSPkg80;
using Microsoft.Win32;
using System.Windows.Forms;

[assembly:ClassInterface(ClassInterfaceType.AutoDual)]

namespace DTSCustomTask
{
	
	[Guid("6D1C10A0-0BF0-4795-91A3-ADF258DCD528"), ComVisible(true)] //GUID is created by using GUIDGEN.EXE
	[ProgId("DTS.SimpleCustomTask")]
	public class SimpleCustomTask : CustomTask
	{
		private string name;
		private string description;

		public SimpleCustomTask()
		{
			name = "";
			description = "SimpleCustomTask description";
		}

		public void Execute(object pPackage, object pPackageEvents, object pPackageLog, ref Microsoft.SQLServer.DTSPkg80.DTSTaskExecResult pTaskResult) 
		{
				try 
		  { 
			  DoExecute(pPackage, pPackageEvents, pPackageLog, ref pTaskResult); 
		  } 
		  finally 
		  { 
			  if ( pPackageLog != null && Marshal.IsComObject(pPackageLog) ) 
				  Marshal.ReleaseComObject(pPackageLog); 
			  if ( pPackageEvents != null && Marshal.IsComObject(pPackageEvents) ) 
				  Marshal.ReleaseComObject(pPackageEvents); 
		  } 
		} 
		
	
		public void DoExecute(object pPackage, object pPackageEvents, object pPackageLog, ref Microsoft.SQLServer.DTSPkg80.DTSTaskExecResult pTaskResult) 
		{ 
			//Assume failure at the outset 
			pTaskResult = DTSTaskExecResult.DTSTaskExecResult_Failure; 
			try 
			{
					Package2 package = (Package2) pPackage; 
				PackageEvents packageEvents = (PackageEvents) pPackageEvents; 
				PackageLog packageLog = (PackageLog) pPackageLog; 
				MessageBox.Show(description); 
			} 
				//First catch COM exceptions and then all other exceptions 
			catch(System.Runtime.InteropServices.COMException e) 
			{ 
				Console.WriteLine(e); 
			} 
			catch(System.Exception e) 
			{ 
				Console.WriteLine(e); 
			} 
			//Return success 
			pTaskResult = DTSTaskExecResult.DTSTaskExecResult_Success; 
		}

		public string Description
		{
			get { return this.description; }
			set { this.description = value; }
		}

		public string Name
		{
			get { return name; }
			set { this.name = value; }
		}
		

		public Microsoft.SQLServer.DTSPkg80.Properties Properties
		{
			get { return null; }
		}

		[System.Runtime.InteropServices.ComVisible(false)]
		override public string ToString()
		{
			return base.ToString();
		}
	
		const string TASK_CACHE = "Software\\Microsoft\\Microsoft SQL Server\\80\\DTS\\Enumeration\\Tasks";
		const string CATID_DTSCustomTask = "{10020200-EB1C-11CF-AE6E-00AA004A34D5}";

		//Registration function for custom task.
		[System.Runtime.InteropServices.ComRegisterFunctionAttribute()]
		static void RegisterServer(Type t)
		{
			try
			{
				
				string guid = "{" + t.GUID.ToString() + "}";
				guid = guid.ToUpper();

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

				RegistryKey root;
				RegistryKey rk;
				RegistryKey nrk;
				
				// 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();

				SimpleCustomTask ct = new SimpleCustomTask();

				root = Registry.ClassesRoot;
				rk = root.OpenSubKey("CLSID\\" + guid, true);
				rk.SetValue("DTSTaskDescription", ct.description);

				nrk.Close();
				rk.Close();
				root.Close();

			}
			catch(Exception e)
			{
				System.Console.WriteLine(e.ToString());
			}
		}

		//Unregistration function for custom task
		[System.Runtime.InteropServices.ComUnregisterFunctionAttribute()]
		static void UnregisterServer(Type t)
		{
			try
			{
				string guid = "{" + t.GUID.ToString() + "}";
				guid = guid.ToUpper();

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

				RegistryKey root;
				RegistryKey rk;

				// 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(Exception e)
			{
				System.Console.WriteLine(e.ToString());
			}
		}
	
	}
}
  5. Add a reference to the RCW that you created in step 1, and then add a reference to System.Windows.Forms.dll.
  6. Build the custom task. The output is a .NET assembly.
  7. Register and install the custom task assembly. This procedure is explained in the "Build, Register, and Install the Custom Task" section in this article.
  8. Create a new DTS package by using DTS Designer in SQL Server Enterprise Manager, and then put the task on the design surface.
  9. Run the package.
back to the top

Additional Information

Remove 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 the log file, call PackageLog.WriteTaskRecord() in the Execute method. This method writes a user-defined message to the package log file and to SQL Server tables. In SQL Server Enterprise Manager, 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: 
if !(pPackageLog == null)
{
	pPackageLog.WriteTaskRecord(0, "Finished executing custom task.");
}
back to the top

Global Variables

You can use the reference to the Package2 object in the Execute method to access global variables. Use the following Visual C# .NET sample code to do this: 
string s = (string)package.GlobalVariables.Item("GlobalVariableName").Value;
back to the top

Change the 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. The following Visual C# .NET code sets the Transact-SQL statement that the Execute SQL task executes: 
Task task = package.Tasks.Item("TaskName");
CustomTask cutask = task.CustomTask;
cutask.Properties.Item("SQLStatement").Value = "select * from orders";
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 also works
	tsk.Description ="Description of custom task"
	Main = DTSTaskExecResult_Success
End Function
back to the top

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:

319985 How to handle Data Transformation Services package events in Visual C# .NET

back to the top

Threading Model

You do not have to run custom tasks that you created by using Visual C# .NET on the main thread. If you run a package that contains a custom task with an Execute Package task, you do not have to use either the inner or the outer package steps to run on the main thread.

back to the top

REFERENCES

For custom task samples written in earlier versions of Microsoft Visual Basic and Microsoft Visual C++, visit the following Microsoft Web sites: back to the top
Modification Type:MajorLast Reviewed:5/22/2006
Keywords:kbHOWTOmaster KB326909 kbAudDeveloper
©2004 Microsoft Corporation. All rights reserved.