Knowledge Base

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("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

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(" 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

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

REFERENCES

For additional information about how to use DTS Packages from Visual Basic and Active Server Pages (ASP), click the article numbers below to view the articles in the Microsoft Knowledge Base:

242391 INF: DTS Package Development, Deployment, and Performance

221193 HOW TO: Install Data Transformation Services (DTS) Event Handlers in Visual Basic

240221 INF: How To Handle Errors in DTS Package and Step Objects

252987 INF: Execute a SQL Server DTS Package from Active Server Pages

For additional information about known problems that are encountered when DTS Packages are executed from Visual Basic, click the article numbers below to view the articles in the Microsoft Knowledge Base:

271889 PRB: Error Message: 'Exception Access Violation 2147221499...'

319048 PRB: Cannot Use DTS Events in VB with Execute Package Task

319058 BUG: DTS Package Execution Canceled Unexpectedly in Visual Basic

251229 FIX: GetExecutionErrorInfo and OnError May Not Return All Errors

back to the top

Keywords: _ik503 _ik5804 kbhowtomaster kbmanaged KB321525

Microsoft KB Archive/321525

Contents

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

IN THIS TASK

SUMMARY

Description of the Technique

Requirements

Sample

Troubleshooting

REFERENCES