HOW TO: Use DTS Package Events in Visual Basic .NET (321525)

SUMMARY

This article describes how to implement Data Transformation Services (DTS) package events in a Visual Basic .NET application, and how to avoid a known issue with COM Interop and DTS Package events.

The Microsoft DTSPackage Object (COM) Library expects a client program to provide a single connection point for events; that is, a single event sink object for the DTS PackageEvents source interface. However, when you import the DTS Type Library to a Microsoft Visual Studio .NET application by setting a reference in the integrated development environment (IDE), or by manually using Type Library Importer (TLBIMP), the importer generates a separate event sink object for each event, which leads to unexpected behavior during run time.

back to the top

Description of the Technique

Use the following methods of the System.Runtime.InteropServices class to connect a separate event sink class as the single event sink object that is expected by the DTS PackageEvents source interface.

back to the top

Requirements

The following list outlines the recommended hardware, software, network infrastructure, and service packs that you will need:

• Microsoft Visual Studio .NET installed on a compatible Microsoft Windows operating system.
• An available instance of Microsoft SQL Server 2000 or SQL Server 7.0.

This article assumes that you have at least basic familiarity with the following topics:

• Visual Basic .NET development.
• SQL Server Data Transformation Services (DTS).

back to the top

Sample

1. Open Visual Studio .NET, create a new Visual Basic Console Application project, and then open the code window for the default Module1.
2. Set a reference to the DTSPackage Object (COM) Library , and insert the following Imports statements at the top of the module:

   Imports System.Runtime.InteropServices
   Imports DTS
   					

3. Insert the following code in the Sub Main procedure:

           Dim pkg As DTS.Package
           Try
               pkg = New DTS.Package()
               'Begin - set up events sink
               Dim cpContainer As UCOMIConnectionPointContainer
               cpContainer = CType(pkg, UCOMIConnectionPointContainer)
               Dim cpPoint As UCOMIConnectionPoint
               Dim PES As PackageEventsSink = New PackageEventsSink()
               Dim guid As Guid = _
                   New Guid("10020605-EB1C-11CF-AE6E-00AA004A34D5")
               cpContainer.FindConnectionPoint(guid, cpPoint)
               Dim intCookie As Integer
               cpPoint.Advise(PES, intCookie)
               'End - set up events sink
               pkg.LoadFromSQLServer("<server>", "<user>", "<password>", _
                   DTSSQLServerStorageFlags.DTSSQLStgFlag_Default, _
                   "", "", "", "<package name>", Nothing)
               Console.WriteLine("PACKAGE EXECUTION BEGINNING")
               pkg.Execute()
               Console.WriteLine("PACKAGE EXECUTION COMPLETED")
               Console.WriteLine("The package contained {0} steps.", _
                   pkg.Steps.Count.ToString)
               pkg.UnInitialize()
               pkg = Nothing
               cpPoint.Unadvise(intCookie)
               cpPoint = Nothing
               cpContainer = Nothing
               PES = Nothing
           Catch exc As System.Runtime.InteropServices.COMException
               Console.WriteLine(exc.Message)
           Catch exc As Exception
               Console.WriteLine(exc.Message)
           Finally
               Console.ReadLine()
           End Try
   					

4. Select or create a DTS package for use in this test, and then edit the values as appropriate in the LoadFromSQLServer method for the server name, user ID, password, and package name.
5. Under the End Module statement, insert the following events sink class to handle the DTS Package events:

   Public Class PackageEventsSink
       Implements DTS.PackageEvents
       Overridable Overloads Sub OnError(ByVal EventSource As String, _
               ByVal ErrorCode As Integer, ByVal Source As String, _
               ByVal Description As String, ByVal HelpFile As String, _
               ByVal HelpContext As Integer, ByVal IDofInterfaceWithError As String, _
               ByRef pbCancel As Boolean) Implements DTS.PackageEvents.OnError
           Console.WriteLine(" OnError in {0}; ErrorCode = {1}, Source = {2}," & _
               " Description = {3}", EventSource, ErrorCode, Source, Description)
       End Sub
       Overridable Overloads Sub OnFinish(ByVal EventSource As String) _
               Implements DTS.PackageEvents.OnFinish
           Console.WriteLine(" OnFinish in {0}", EventSource)
       End Sub
       Overridable Overloads Sub OnProgress(ByVal EventSource As String, _
               ByVal ProgressDescription As String, ByVal PercentComplete As Integer, _
               ByVal ProgressCountLow As Integer, ByVal ProgressCountHigh As Integer) _
               Implements DTS.PackageEvents.OnProgress
           Console.WriteLine(" OnProgress in {0}; ProgressDescription = {1}", _
               EventSource, ProgressDescription)
       End Sub
       Overridable Overloads Sub OnQueryCancel(ByVal EventSource As String, _
               ByRef pbCancel As Boolean) Implements DTS.PackageEvents.OnQueryCancel
           If EventSource.Length > 0 Then
               Console.WriteLine(" OnQueryCancel in {0}; pbCancel = {1}", _
                   EventSource, pbCancel.ToString)
           Else
               Console.WriteLine(" OnQueryCancel; pbCancel = {0}", pbCancel.ToString)
           End If
           pbCancel = False
       End Sub
       Overridable Overloads Sub OnStart(ByVal EventSource As String) _
               Implements DTS.PackageEvents.OnStart
           Console.WriteLine(" OnStart in {0}", EventSource)
       End Sub
   End Class
   					

6. Run the package and observe the sequence of steps and events. Click Enter to close the console window and quit the program.

back to the top

Troubleshooting

• Do not try to implement DTS Package events by selecting the available events one at a time from the drop-down list boxes at the top of the code window. This can cause unexpected behavior at run time as described in the "Summary" section of this article.
• The OnQueryCancel event is provided to allow you to cancel execution of the package if you want. However, in some circumstances the Boolean flag pbCancel is set to "True" (without quotation marks) when this event is raised, without user intervention, and package execution is canceled unexpectedly. For this reason, the sample resets the value to "False" (without quotation marks) each time the OnQueryCancel event is raised.

back to the top