Handling Events Programmatically
The SSIS runtime provides a collection of events that occur before, during, and after the validation and execution of a package. These events can be captured in two ways. The first method is by implementing the IDTSEvents interface in a class, and supplying the class as a parameter to the Execute and Validate methods of the package. The second method is by creating DtsEventHandler objects, which can contain other SSIS objects, such as tasks and loops, that are executed when an event in IDTSEvents occurs. This section describes these two methods and provides code examples to demonstrate their use.
Developers who build and execute packages programmatically can receive event notifications during the validation and execution process using the IDTSEvents interface. This is done by creating a class that implements the IDTSEvents interface and providing this class as a parameter to the Validate and Execute methods of a package. The methods of the class are then called by the run-time engine when the events occur.
The DefaultEvents class is a class that already implements the IDTSEvents interface; therefore, another alternative to implementing IDTSEvents directly is to derive from DefaultEvents and override the specific events that you want to respond to. You then provide your class as a parameter to the Validate and Execute methods of the Package to receive event callbacks.
The following code sample demonstrates a class that derives from DefaultEvents, and overrides the OnPreExecute method. The class is then provided as aparameter to the Validate and Execute methods of the package.
Imports Microsoft.SqlServer.Dts.Runtime Module Module1 Sub Main() Dim p As Package = New Package() Dim eventsClass As MyEventsClass = New MyEventsClass() p.Validate(Nothing, Nothing, eventsClass, Nothing) p.Execute(Nothing, Nothing, eventsClass, Nothing, Nothing) Console.Read() End Sub End Module Class MyEventsClass Inherits DefaultEvents Public Overrides Sub OnPreExecute(ByVal exec As Executable, ByRef fireAgain As Boolean) ' TODO: Add custom code to handle the event. Console.WriteLine("The PreExecute event of the " & _ exec.ToString() & " has been raised.") End Sub End Class
The run-time engine provides a robust, highly flexible event handling and notification system through the DtsEventHandler object. These objects let you design whole workflows within the event handler, which execute only when the event that the event handler belongs to occurs. The DtsEventHandler object is a container that is executed when the corresponding event on its parent object fires. This architecture lets you create isolated workflows that are executed in response to events that occur on a container. Because DtsEventHandler objects are synchronous, execution does not resume until the event handlers that are attached to the event have returned.
The following code demonstrates how to create a DtsEventHandler object. The code adds a FileSystemTask to the Executables collection of the package, and then creates a DtsEventHandler object for the OnError event of the task. A FileSystemTask is added to the event handler, which is executed when the OnError event occurs for the first FileSystemTask. This example assumes that you have a file that is named C:\Windows\Temp\DemoFile.txt for testing. The first time that you run the sample, if copies the file successfully and the event handler is not called. The second time you run the sample, the first FileSystemTask fails to copy the file (because the value of OverwriteDestinationFile is false), the event handler is called, the second FileSystemTask deletes the source file, and the package reports failure because of the error that occurred.
Imports System.IO Imports Microsoft.SqlServer.Dts.Runtime Imports Microsoft.SqlServer.Dts.Tasks.FileSystemTask Module Module1 Sub Main() Dim f As String = "DemoFile.txt" Dim e As Executable Dim th As TaskHost Dim fst As FileSystemTask Dim p As Package = New Package() p.Variables.Add("sourceFile", True, String.Empty, _ "C:\Windows\Temp\" & f) p.Variables.Add("destinationFile", True, String.Empty, _ Path.Combine(Directory.GetCurrentDirectory(), f)) ' Create a first File System task and add it to the package. ' This task tries to copy a file from one folder to another. e = p.Executables.Add("STOCK:FileSystemTask") th = CType(e, TaskHost) th.Name = "FileSystemTask1" fst = CType(th.InnerObject, FileSystemTask) fst.Operation = DTSFileSystemOperation.CopyFile fst.OverwriteDestinationFile = False fst.Source = "sourceFile" fst.IsSourcePathVariable = True fst.Destination = "destinationFile" fst.IsDestinationPathVariable = True ' Add an event handler for the FileSystemTask's OnError event. Dim ehOnError As DtsEventHandler = CType(th.EventHandlers.Add("OnError"), DtsEventHandler) ' Create a second File System task and add it to the event handler. ' This task deletes the source file if the event handler is called. e = ehOnError.Executables.Add("STOCK:FileSystemTask") th = CType(e, TaskHost) th.Name = "FileSystemTask1" fst = CType(th.InnerObject, FileSystemTask) fst.Operation = DTSFileSystemOperation.DeleteFile fst.Source = "sourceFile" fst.IsSourcePathVariable = True Dim vr As DTSExecResult = p.Validate(Nothing, Nothing, Nothing, Nothing) Console.WriteLine("ValidationResult = " + vr.ToString()) If vr = DTSExecResult.Success Then Dim er As DTSExecResult = p.Execute(Nothing, Nothing, Nothing, Nothing, Nothing) Console.WriteLine("ExecutionResult = " + er.ToString()) If er = DTSExecResult.Failure Then If Not File.Exists("C:\Windows\Temp\" + f) Then Console.WriteLine("Source file has been deleted by the event handler.") End If End If End If Console.Read() End Sub End Module