DTS Custom Task in C#

During last year I wrote an article about DTS and how to use it in C#. In that article I stated that I didn't manage to solve the problem related to CustomTask. Today I finally forced myself to tackle that problem again and here is the result. Writing simple CustomTask in C# is the easiest part. We open new Class Library project and name it 'DTSCustomTask'. After that we need reference to Microsoft DTSPackage Object Library. Using VS.NET it is easy, you will find it under COM tab. If you need to do that manually use the following from command line :

tlbimp "C:\Program Files\Microsoft SQL Server\80\Tools\Binn\dtspkg.DLL"

If your .NET framework-bin is not included in path (don't tell that to anybody), browse to tlbimp.EXE and execute it from bin folder or rather include it in path variable. You will see the message:

Microsoft (R) .NET Framework Type Library to Assembly Converter 1.0.3705.0
Copyright (C) Microsoft Corporation 1998-2001. All rights reserved.

Type library imported to DTS.dll

And in directory where the command is executed will be that DTS.dll. Code for simple custom task looks like this :

[ProgId("CustomTask_NET.CCustomTask")]
public class CCustomTask:CustomTask
{
string name;
string description;
public void Execute(object pPackage, object pPackageEvents,
object pPackageLog, ref DTSTaskExecResult pTaskResult)
{
pTaskResult = DTSTaskExecResult.DTSTaskExecResult_Success;
MessageBox.Show("Managed minimalist custom task in action.",
".NET Message Box", MessageBoxButtons.OK, MessageBoxIcon.Information);
}
public Properties Properties
{
get
{
return null;
}
}
public string Description
{
get
{
return description;
}
set
{
description=value;
}
}
public string Name
{
get
{
return name;
}
set
{
name=value;
}
}
}

Except that attribute which states what we want to be ProgId for interop purposes, everything is very ordinary. All properties and method are the result of implementation of CustomTask interface. Now we can compile it and start with so-called DLL Hell. To use it from DTS package we must make it available for COM. The best tool for testing if something is COM compliant is VB6, so I will test it against VB6. To register assembly for COM we will use regasm tool from command line and from directory where is that assembly. Command looks like this :

regasm DTSCustomTask.dll /tlb:DTSCustomTask.tlb

We need DTSCustomTask.tlb to add reference to VB6 project. Upon execution open regedit and use 'CustomTask_NET.CCustomTask' as search string. That will save you few hours of frustration. When you find it under :

[HKEY_CLASSES_ROOT\CLSID\{6A1CF1CE-2C80-378A-B71B-F6D3AA9DA181}]

Or whatever that CLSID is in your case, take a look at InprocServer32, it must contain all these entries :

@="C:\\WINNT\\System32\\mscoree.dll"
"ThreadingModel"="Both"
"Class"="DTSCustomTask.CCustomTask"
"Assembly"="DTSCustomTask, Version=1.0.1099.28310, Culture=neutral, PublicKeyToken=null"
"RuntimeVersion"="v1.0.3705"
"CodeBase"="file:///C:/Documents and Settings/Bule/My Documents/Visual Studio Projects/DTSCustomTask
/bin/Debug/DTSCustomTask.DLL"

During many recompilations and registrations I found out that CodeBase is never there and default ((Default)-REG_SZ is C:\WINNT\System32\mscoree.dll, first value) is also sometimes missing. Don't forget to change path for CodeBase. If you have all six values it is time to open VB6. As you know, Enterprise Manager for SQL server allows saving of DTS package as bas file. First we will create simple VB6 custom task. That is slightly modified "DTS Example: Basic Custom Task in Visual Basic", you can find it on MSDN. I just changed first line from :

Private mstrTaskName As String

to :

Public Name As String

Also change accordingly properties code. Now when we compile it using Enterprise Manager we will create package, register custom task and save it as Visual Basic file. That bas file will be boilerplate for test of our managed custom task. After few changes it will look like this :

Attribute VB_Name = "Module1"
Option Explicit
Public goPackageOld As New DTS.Package
Public goPackage As DTS.Package2
Private Sub Main()
goPackage = goPackageOld
goPackage.Name = "CS.NET"
goPackage.WriteCompletionStatusToNTEventLog = False
goPackage.FailOnError = False
goPackage.PackagePriorityClass = 2
goPackage.MaxConcurrentSteps = 4
goPackage.LineageOptions = 0
goPackage.UseTransaction = True
goPackage.TransactionIsolationLevel = 4096
goPackage.AutoCommitTransaction = True
goPackage.RepositoryMetadataOptions = 0
goPackage.UseOLEDBServiceComponents = True
goPackage.LogToSQLServer = False
goPackage.LogServerFlags = 0
goPackage.FailPackageOnLogFailure = False
goPackage.ExplicitGlobalVariables = False
goPackage.PackageType = 0
'---------------------------------------------------------------------------
' create package steps information
'---------------------------------------------------------------------------
Dim oStep As DTS.Step2
Dim oPrecConstraint As DTS.PrecedenceConstraint
'------------- a new step defined below
oStep = goPackage.Steps.New
oStep.Name = ".NET_Task"
oStep.ExecutionStatus = 1
oStep.TaskName = ".NET_Task"
oStep.CommitSuccess = False
oStep.RollbackFailure = False
oStep.ScriptLanguage = "VBScript"
oStep.AddGlobalVariables = True
oStep.RelativePriority = 3
oStep.CloseConnection = False
oStep.ExecuteInMainThread = True
oStep.IsPackageDSORowset = False
oStep.JoinTransactionIfPresent = False
oStep.DisableStep = False
oStep.FailPackageOnError = False
goPackage.Steps.Add(oStep)
oStep = Nothing
'---------------------------------------------------------------------------
' create package tasks information
'---------------------------------------------------------------------------
'------------- call Task_Sub1 for task DTSTask_DTSBasic.CustTask_1
Call Task_Sub1(goPackage)
'---------------------------------------------------------------------------
' Save or execute package
'---------------------------------------------------------------------------
'goPackage.SaveToSQLServer "(local)", "sa", ""
goPackage.Execute()
goPackage.UnInitialize()
'to save a package instead of executing it, comment out the executing package line above
and uncomment
the saving package line
goPackage = Nothing
goPackageOld = Nothing
End Sub
'------------- define Task_Sub1 for task DTSTask_DTSBasic.CustTask_1
Public Sub Task_Sub1(ByVal goPackage As Object)
Dim oTask As DTS.Task
Dim oCustomTask1 As DTSCustomTask.CCustomTask
oTask = goPackage.Tasks.New("CustomTask_NET.CCustomTask")
oCustomTask1 = oTask.CustomTask
oCustomTask1.Name = ".NET_Task"
goPackage.Tasks.Add(oTask)
oCustomTask1 = Nothing
oTask = Nothing
End Sub

Save this as bas file and open with VB6, add references to DTSCustomTask.tlb and Microsoft DTSPackage Object Library. When you run it, managed message box from DTSCustomTask assembly will appear, if everything is OK. Translation of that bas file to C# looks like this :

[STAThread]
static void Main(string[] args)
{
Package2Class package=new Package2Class();
package.Name = "Package .NET";
package.WriteCompletionStatusToNTEventLog = false;
package.FailOnError = false;
package.PackagePriorityClass = DTSPackagePriorityClass.DTSPriorityClass_Normal;
package.MaxConcurrentSteps = 4;
package.LineageOptions = 0;
package.UseTransaction = true;
package.TransactionIsolationLevel = DTSIsolationLevel.DTSIsoLevel_CursorStability;
package.AutoCommitTransaction = true;
package.RepositoryMetadataOptions = 0;
package.UseOLEDBServiceComponents = true;
package.LogToSQLServer = false;
package.LogServerFlags = 0;
package.FailPackageOnLogFailure = false;
package.ExplicitGlobalVariables = false;
package.PackageType = 0;
DTS.Step2 step = (DTS.Step2)package.Steps.New();
step.Name = "DTSStep_DTSBasic.CustTask_1";
step.ExecutionStatus = DTSStepExecStatus.DTSStepExecStat_Waiting;
step.TaskName = ".NET_Task";
step.CommitSuccess = false;
step.RollbackFailure = false;
step.ScriptLanguage = "VBScript";
step.AddGlobalVariables = true;
step.RelativePriority = DTSStepRelativePriority.DTSStepRelativePriority_Normal;
step.CloseConnection = false;
step.ExecuteInMainThread = true;
step.IsPackageDSORowset = false;
step.JoinTransactionIfPresent = false;
step.DisableStep = false;
step.FailPackageOnError = false;
package.Steps.Add(step);
step = null;
LoadTask(package);
// object MIA=System.Reflection.Missing.Value;
// package.SaveToSQLServer("(local)\\NetSDK", "sa", "",
DTS.DTSSQLServerStorageFlags.DTSSQLStgFlag_Default, "",
"","",ref MIA,false);
package.Execute();
package.UnInitialize();
package = null;
}
static void LoadTask(Package2 p)
{
DTS.Task t;
DTSCustomTask.CCustomTask ct;
t = p.Tasks.New("CustomTask_NET.CCustomTask");
ct = (DTSCustomTask.CCustomTask)t.CustomTask;
ct.Name = ".NET_Task";
p.Tasks.Add(t);
ct = null;
t = null;
}

Paste this inside new Console Application and add reference to Microsoft DTSPackage Object Library and this time to DTSCustomTask.dll. Compile and run. If you want, uncomment two commented lines to save package. If you open saved package in Enterprise Manager there won't be anything-it will appear empty, but when you execute the package message box will show up. For the end, don't try to make something which is not custom task to be one via casting, it works only in VB6 (even without explicit cast), in .NET you will get 'System.InvalidCastException' with 'QueryInterface for interface DTS.CustomTask failed.'