HOW TO: Handle Data Transformation Services Package Events in Visual C# .NET (319985)

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);
		}

	} 

}
				

back to the top