Article ID: 319985
Article Last Modified on 2/25/2004
APPLIES TO
- Microsoft SQL Server 2000 Standard Edition
- Microsoft SQL Server 2000 64-bit Edition
- Microsoft Visual C# .NET 2002 Standard Edition
This article was previously published under Q319985
SUMMARY
This article demonstrates how to handle Data Transformation Services (DTS) package events from Microsoft Visual C# .NET.
With the .NET Framework, do not use delegates to handle events with DTS objects. To properly handle the events, the Visual C# .NET application must implement the IConnectionPointContainer and IConnectionPoint interfaces. The .NET Framework provides managed definitions of these interfaces through the UCOMIConnectionPointContainer and UCOMIConnectionPoint interfaces. These interfaces are part of the System.Runtime.InteropServices namespace.
back to the top
Prerequisites
Create a DTS package, and then save it to SQL Server before you insert the following sample code in a Microsoft Visual C# .NET Console Application project.
back to the top
Steps to Use Visual C# .NET to Handle DTS Events
The following sample code executes a DTS package that is stored in SQL Server. When an event is fired, a message is sent to the console. Inside the Visual C# .NET project, add a reference to the Microsoft DTSPackage Object Library version 2.0 COM object, and then insert the following code in the C# class file for your project:
using System;
using System.Runtime.InteropServices;
using DTS;
namespace DtsInterop
{
//This class loads and executes the DTS package.
class ExecPkgWithEvents
{
/* Prior to running this code, create a DTS package and save it to SQL Server. Then set a reference to
// the DTSPackage Object Library version 2.0 COM object.
*/
public Package2Class package;
[MTAThread]
static void Main(string[] args)
{
ExecPkgWithEvents app = new ExecPkgWithEvents();
app.Run();
}
public void Run()
{
try
{
package = new Package2Class();
UCOMIConnectionPointContainer CnnctPtCont = (UCOMIConnectionPointContainer) package;
UCOMIConnectionPoint CnnctPt;
PackageEventsSink PES = new PackageEventsSink ();
Guid guid = new Guid("10020605-EB1C-11CF-AE6E-00AA004A34D5"); // UUID of PackageEvents Interface
CnnctPtCont.FindConnectionPoint(ref guid, out CnnctPt);
int iCookie;
CnnctPt.Advise(PES, out iCookie);
object pVarPersistStgOfHost = null;
package.LoadFromSQLServer("PNXDEVBOX", null, null, DTSSQLServerStorageFlags.DTSSQLStgFlag_UseTrustedConnection, null,
null, null, "Test", ref pVarPersistStgOfHost);
package.Execute();
package.UnInitialize();
package = null;
CnnctPt.Unadvise(iCookie); //a connection that is created by IConnectionPoint.Advise must be closed by calling IConnectionPoint.Unadvise to avoid a memory leak
}
catch(System.Runtime.InteropServices.COMException ex)
{
Console.WriteLine("COMException {0}\n{1}\n{2}", ex.ErrorCode, ex.Message, ex.StackTrace);
}
catch(System.Exception ex)
{
Console.WriteLine("Exception\n{0}\n{1}", ex.Message, ex.StackTrace);
}
}
}
//This class is responsible for handling DTS Package events. When an event is fired, a message is sent to
//the console.
class PackageEventsSink : DTS.PackageEvents
{
public void OnQueryCancel(string EventSource, ref bool pbCancel)
{
Console.WriteLine("OnQueryCancel({0})", EventSource);
pbCancel = false;
}
public void OnStart(string EventSource)
{
Console.WriteLine("OnStart({0})", EventSource);
}
public void OnProgress(string EventSource, string ProgressDescription, int PercentComplete, int ProgressCountLow, int ProgressCountHigh)
{
Console.WriteLine("OnProgress({0}, {1}, {2}, {3}, {4})", EventSource, ProgressDescription,
PercentComplete, ProgressCountLow, ProgressCountHigh);
}
public void OnError(string EventSource, int ErrorCode, string Source, string Description, string HelpFile, int HelpContext, string
IDofInterfaceWithError, ref bool pbCancel)
{
Console.WriteLine("OnError({0}, {1}, {2}, {3}, {4}, {5})", EventSource, ErrorCode, Source, Description,
HelpFile, HelpContext);
pbCancel = false;
}
public void OnFinish(string EventSource)
{
Console.WriteLine("OnFinish({0})", EventSource);
}
}
}
Keywords: kbhowtomaster KB319985
