During last year I wrote an article about DTS and how to use it in VB.NET. 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 VB 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
Inherits CustomTask
Private name As String
Private description As String
Public Sub Execute(pPackage As Object, pPackageEvents As Object,
pPackageLog As Object, ByRef pTaskResult As DTSTaskExecResult)
pTaskResult = DTSTaskExecResult.DTSTaskExecResult_Success
MessageBox.Show("Managed minimalist custom task in action.",
".NET Message Box",
MessageBoxButtons.OK, MessageBoxIcon.Information)
End Sub 'Execute
Public ReadOnly Property Properties() As Properties
Get
Return Nothing
End Get
End Property
Public Property Description() As String
Get
Return description
End Get
Set
description = value
End Set
End Property
Public Property Name() As String
Get
Return name
End Get
Set
name = value
End Set
End Property
End Class 'CCustomTask
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 VB looks
like this :
'Entry point
which delegates to vb-style main Private Function
Public Overloads Shared Sub Main()
Main(System.Environment.GetCommandLineArgs())
End Sub
Overloads Shared Sub
Main(args() As String)
Dim package As 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
Dim
step As DTS.Step2 =
CType(package.Steps.New(), DTS.Step2)
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
= Nothing
LoadTask(package)
' Dim MIA As Object = System.Reflection.Missing.Value
' package.SaveToSQLServer("(local)\NetSDK", "sa", "",
DTS.DTSSQLServerStorageFlags.DTSSQLStgFlag_Default,
"", "", "",
MIA, False);
package.Execute()
package.UnInitialize()
package =
Nothing
End Sub 'Main
Shared Sub LoadTask(p As
Package2)
Dim t As
DTS.Task
Dim ct As
DTSCustomTask.CCustomTask
t = p.Tasks.New("CustomTask_NET.CCustomTask")
ct =
CType(t.CustomTask, DTSCustomTask.CCustomTask)
ct.Name =
".NET_Task"
p.Tasks.Add(t)
ct =
Nothing
t =
Nothing
End Sub 'LoadTask
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.'.