Microsoft KB Archive/321525

= HOW TO: Use DTS Package Events in Visual Basic .NET =

Article ID: 321525

Article Last Modified on 10/16/2006

-

APPLIES TO


 * Microsoft Visual Basic .NET 2003 Standard Edition
 * Microsoft Visual Basic .NET 2002 Standard Edition
 * Microsoft SQL Server 2000 Standard Edition
 * Microsoft SQL Server 2000 64-bit Edition
 * Microsoft SQL Server 7.0 Standard Edition

-



This article was previously published under Q321525



For a Microsoft Visual C# .NET version of this article, see 319985.

IN THIS TASK
SUMMARY
 * Description of the Technique
 * Requirements
 * Sample
 * Troubleshooting

REFERENCES



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
 Open Visual Studio .NET, create a new Visual Basic Console Application project, and then open the code window for the default Module1.  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   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(&quot;10020605-EB1C-11CF-AE6E-00AA004A34D5&quot;) cpContainer.FindConnectionPoint(guid, cpPoint) Dim intCookie As Integer cpPoint.Advise(PES, intCookie) 'End - set up events sink pkg.LoadFromSQLServer(&quot; &quot;, &quot; &quot;, &quot; &quot;, _               DTSSQLServerStorageFlags.DTSSQLStgFlag_Default, _                &quot;&quot;, &quot;&quot;, &quot;&quot;, &quot; &quot;, Nothing) Console.WriteLine(&quot;PACKAGE EXECUTION BEGINNING&quot;) pkg.Execute Console.WriteLine(&quot;PACKAGE EXECUTION COMPLETED&quot;) Console.WriteLine(&quot;The package contained {0} steps.&quot;, _               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  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.  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(&quot; OnError in {0}; ErrorCode = {1}, Source = {2},&quot; & _           &quot; Description = {3}&quot;, EventSource, ErrorCode, Source, Description) End Sub Overridable Overloads Sub OnFinish(ByVal EventSource As String) _ Implements DTS.PackageEvents.OnFinish Console.WriteLine(&quot; OnFinish in {0}&quot;, 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(&quot; OnProgress in {0}; ProgressDescription = {1}&quot;, _           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(&quot; OnQueryCancel in {0}; pbCancel = {1}&quot;, _               EventSource, pbCancel.ToString) Else Console.WriteLine(&quot; OnQueryCancel; pbCancel = {0}&quot;, pbCancel.ToString) End If       pbCancel = False End Sub Overridable Overloads Sub OnStart(ByVal EventSource As String) _ Implements DTS.PackageEvents.OnStart Console.WriteLine(&quot; OnStart in {0}&quot;, EventSource) End Sub End Class  Run the package and observe the sequence of steps and events. Click Enter to close the console window and quit the program.</ol>

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 &quot;Summary&quot; 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 &quot;True&quot; (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 &quot;False&quot; (without quotation marks) each time the OnQueryCancel event is raised.

back to the top

<div class="references_section">