Event Handling

Each task and container raises events as it runs, such as an OnError event, among several others that will be discussed shortly. SSIS allows you to trap and handle these events by setting up workflows that will run when particular events fire.

The first really interesting thing about Event Handlers is that there is a whole tab devoted to them in the SSIS package design environment. Figure 13-8 shows the Event Handler tab right next to the Control Flow and Data Flow tabs that you have worked with up to now. The Event Handler design area is just like the Control Flow area — you can use the same component types and do anything that is possible at the Control Flow level. Once several event handlers have been added to a package, the workflow could get very complicated and difficult to understand if you had to view it all at once, so separating Event Handlers from the Control Flow makes sense. It is important, however, to make sure your packages are well designed and documented because an Event Handler that was set up and then forgotten could be the source of a hard-to-troubleshoot problem within the package.

Figure 13-8

Events

As the package and each task or container executes, a dozen different events are raised. You can capture the events by adding Event Handlers that will run when the event fires. The OnError event may be the event most frequently handled, but some of the other events will be useful in complex ETL packages. Events can also be used to set breakpoints and control logging, which will all be covered later in the chapter.

The following table shows a list of all of the events.

Event	Description
OnError	The OnError event is raised whenever an error occurs. You can use this event to capture errors instead of using the failure precedence constraint to redirect the workflow.
OnExecStatusChanged	Each time the execution status changes on a task or container, this event fires.
OnInformation	During the validation and execution events of the tasks and containers, this event reports information. This is the information displayed in the Progress tab.
OnPostExecute	Just after task or container execution completes, this event fires. You could use this event to clean up work tables or delete no-longer-needed files.
OnPostValidate	This event fires after validation of the task is complete.
OnPreExecute	Just before a task or container runs, this event fires. This event could be used to check the value of a variable before the task executes.
OnPreValidate	Before validation of a task begins, this event fires.
OnProgress	As measurable progress is made, this event fires. The information about the progress of an event can be viewed in the Progress tab.
OnQueryCancel	The OnQueryCancel event is raised when an executable checks to see if it should stop or continue running.
OnTaskFailed	It's possible for a task or container to fail without actual errors. You can trap that condition with this event.
OnVariableValueChanged	Any time a variable value changes, this event fires. Setting the RaiseChangeEvent property to False prevents this event from firing. This event will be very useful when debugging a package.
OnWarning	Warnings are less critical than errors. This event fires when a warning occurs. Warnings are displayed in the Progress tab.

Inventory Example

This example will demonstrate how to use Event Handlers by setting up a simulation that checks the inventory status of some random products from AdventureWorks. Run this script in SQL Server Management Studio against the AdventureWorks database to create the tables and a stored procedure used in the example:

 USE AdventurWorks GO CREATE TABLE InventoryCheck (     ProductID INT) GO CREATE TABLE InventoryWarning (     ProductID INT, ReorderQuantity INT ) GO CREATE TABLE MissingProductID (     ProductID INT ) GO CREATE PROC usp_GetReorderQuantity @ProductID INT,     @ReorderQuantity INT OUTPUT AS     IF NOT EXISTS(SELECT ProductID FROM Production.ProductInventory             WHERE ProductID = @ProductID) BEGIN         RAISERROR('InvalidID',16,1)         RETURN 1     END     SELECT @ReorderQuantity = SafetyStockLevel - SUM(Quantity)     FROM Production.Product AS p     INNER JOIN Production.ProductInventory AS i     ON p.ProductID = i.ProductID     WHERE p.ProductID = @ProductID     GROUP BY p.ProductID, SafetyStockLevel     RETURN 0 GO 

Create a new SSIS package. Add a Connection Manager pointing to the AdventureWorks database using the ADO.NET provider. This example uses the Execute SQL task with parameters. The parameters work differently depending on which provider is being used. For example, parameters used with the OLE DB provider are numerically named starting with zero. Parameters used with ADO.NET providers use names beginning with the @ symbol.

Set up the variables in the following table. (Click the Control Flow area right before opening the variables window so that the scope of the variables will be at the Package level.)

Name	Scope	Data Type	Value
Count	Package	Int32	0
ProductID	Package	Int32	0
ReorderQuantity	Package	Int32	0

Drag a Sequence Container to the Control Flow design area and name it Inventory Check. You can use a Sequence Container to group tasks, treating the tasks as a unit in the workflow of the package. In this case, you will use it to experiment with the Event Handlers. Set the MaximumErrorCount property of Inventory Check to 9999 in the Property window. This example will raise errors by design, and setting the MaximumErrorCount property will allow the simulation to continue running after the errors fire.

Drag an Execute SQL task into the Inventory Check container, and name it Empty Tables. Double-click the task to open the Execute SQL Task Editor. First change the ConnectionType property to ADO.NET. Set the Connection property to the Connection Manager pointing to AdventureWorks. Click the ellipsis button next to the SQLStatement property and type the following into the Enter SQL Query window:

 DELETE FROM MissingProductID DELETE FROM InventoryWarning DELETE FROM InventoryCheck 

Click OK to accept the statements and OK once more to accept the Execute SQL Task Editor changes. Drag a For Loop container into the Inventory Check container, and name it Inventory Query Simulator. Double-click the Inventory Query Simulator and fill in the properties as shown in the following table.

Property	Value
InitExpression	@Count =1
EvalExpression	@Count <= 50
AssignExpression	@Count = @Count + 1

Click OK to accept the configuration. Set the MaximumErrorCount property of the Inventory Query Simulator to 9999 in the Properties window. Drag a precedence constraint from the Empty Tables task to the Inventory Query Simulator. Drag a Script task into the Inventory Query Simulator container, and name it Generate ProductID. Double-click to open the Script Task Editor. Select the Script pane. Set the ReadWriteVariables property to User::ProductID, as shown in Figure 13-9.

Figure 13-9

Click Design Script to open the VSA design environment. You will use this Script task to generate a random ProductID. Replace Sub Main with the following code:

 Public Sub Main()     Randomize()     Dts.Variables("User::ProductID").Value = Cint(Int((900 * Rnd()) + 1))     Dts.TaskResult = Dts.Results.Success End Sub 

Close the VSA environment and then click OK to accept the changes to the Script task.

Add an Execute SQL task to the Inventory Query Simulator and name it Check Inventory Level. Drag a Precedence Constraint from Generate ProductID to Check Inventory Level. Double-click the Check Inventory Level task to open the Execute SQL Task Editor. Set the ConnectionType property to ADO.NET. Find the Connection Manager for the AdventureWorks database in the list of Connections. Change the SQLStatement property to usp_GetReorderQuantity. Change the IsQueryStoredProcedure to True. This task will call the usp_GetReorderQuantity with the two parameters. The ResultSet property should be set to None since you are using an output parameter to get the ReorderQuantity value from the stored procedure. The General pane of the Execute SQL Task Editor should resemble Figure 13-10.

Figure 13-10

On the Parameter Mapping pane, set up the parameters as in the following table.

Variable Name	Direction	Data Type	Parameter Name
User::ProductID	Input	Int32	@ProductID
User::ReorderQuantity	Output	Int32	@ReorderQuantity

Click OK to accept the configuration. As described earlier, set the MaximumErrorCount property of the Check Inventory Level task to 9999 using the Properties window.

Add another Execute SQL task and name it Insert Warning. This task will be used to insert a row into the InventoryWarning table whenever the current inventory is less than the established reorder point for a particular product. Connect Check Inventory Level to Insert Warning. Double-click the Precedence Constraint and set the Evaluation operation property to Expression and Constraint. Set the Expression Property to @ReorderQuantity > 0 and leave the Value property at Success (see Figure 13-11). Click OK to accept the changes to the precedence constraint.

Figure 13-11

Double-click the Insert Warning object. Set the ConnectionType to ADO.NET. Choose the AdventureWorks Connection Manager from the Connection list. Click the ellipsis next to SQLStatement and type this into the Enter SQL Query dialog box:

 INSERT INTO InventoryWarning (ProductID, ReorderQuantity) SELECT @ProductID, @ReorderQuantity 

Click OK to accept the command. On the Parameter Mapping pane, set up two parameters, as shown in the following table. In this case they will both be input parameters.

Variable Name	Direction	Data Type	Parameter Name
User::ProductID	Input	Int32	@ProductID
User::ReorderQuantity	Input	Int32	@ReorderQuantity

Click OK to accept the configuration. The package should now resemble Figure 13-12.

Figure 13-12

When you run the package, sometimes the Check Inventory Level task will fail. The Generate ProductID script will not always come up with a valid ProductID. When that happens, the stored procedure will raise an error and cause the Check Inventory Level task to fail. Because the FailParentOnFailure and FailPackageOnFailure properties are set to False by default, and the MaximumErrorCount property is set to 9999 on the task and parent containers, the package will continue to run through the simulation even after a failure of this task.

You will notice that once the Check Inventory Level task fails, it will turn red, but the simulation will continue running. It is a bit misleading, but once the task turns red, it stays red even though it will run each time the For Loop executes. A great way to view what is going on as the package runs is to click the Progress tab. This is also a fantastic troubleshooting tool, with detailed information about each step. Once the package completes and debugging is stopped, you can continue to view the information on the Execution Results tab.

After running the package, you can view the results by querying the InventoryWarning table to see the rows that were inserted when the User::ReorderQuantity variable was greater than 0. Run this query in SQL Server Management Studio:

 SELECT * FROM InventoryWarning 

The package is almost guaranteed to generate some errors at the Check Inventory Level task every time it runs. You could add a task connected to the Check Inventory Level with the Precedence Constraint set to Failure, but in this case you will create an Event Handler to add a row to the MissingProductID table each time the Check Inventory Level task fails.

Click the Event Handlers tab. Because you can have a large number of Event Handlers in a package, you must select the object and the event from the drop-down lists. Click the down arrow under Executable to see the package objects in a hierarchy. The Package has a child, Inventory Check, which has children Empty Tables and Inventory Query Simulator and so on (see Figure 13-13).

Figure 13-13

Select Check Inventory Level and click OK to close the list. Choose OnError in the Event Handler list if it isn't there by default. You must click the link "Click here to create an 'OnExecute' event handler for executable ‘Check Inventory Level'" to create the new event handler. The screen will change to a design area very much like the Control Flow tab. You can now drag any Control Flow level task or container to the design area. In this case you will add an Execute SQL task that adds a row to the MissingProductID table whenever the Check Inventory Level task fails.

Event handlers can be as simple or as complex as you need them to be. All functionality available at the Control Flow level is available at the Event Handler level, including the ability to add an Event Handler to an Event Handler.

Drag an Execute SQL task to the Event Handler design area and name it Insert Missing ProductID. Double-click the task to bring up the Execute SQL Task Editor. Change the Connection Type to ADO.NET. Choose the AdventureWorks Connection Manager from the Connection list. Click the ellipsis next to the SQLStatement property to open the Enter SQL Query dialog box. Type the following statement:

 INSERT INTO MissingProductID (ProductID) SELECT @ProductID 

Click OK to accept the query. Switch to the Parameter Mapping pane. Add one parameter with the properties shown in the following table.

Variable Name	Direction	Data Type	Parameter Name
User::ProductID	Input	Int32	@ProductID

Click OK to accept the configuration. Now, when you run the package, the new Event Handler will fire whenever the Check Inventory Level task raises an error. You can query the MissingProductID table to see the results by running this query in SQL Server 2005 Management Studio:

 SELECT * from MissingProductID 

Suppose you would like to keep a record of all the ProductID numbers that were tested. To do that, add another Event Handler to the Check Inventory Level task. With Check Inventory Level selected in the Executable list, select OnPreExecute under Event Handler. Click the link to create the handler. Add an Execute SQL task to the Event Handler design area and name it Record ProductID. Double-click to open the Execute SQL Task Editor. Change the ConnectionType property to ADO.NET. Select the AdventureWorks Connection Manager from the Connection list. Add this statement to the SQLStatement property by typing in the Property text box or using the Enter SQL Query dialog box:

 INSERT INTO InventoryCheck (ProductID) SELECT @ProductID 

Add one parameter, @ProductID, on the Parameter Mapping pane with exactly the same properties as the one added to the OnError event task, as the following table shows.

Variable Name	Direction	Data Type	Parameter Name
User:ProductID	Input	Int32	@ProductID

Click OK to accept the configuration and run the package. Once execution of the package has completed, go back to SQL Server Management Studio to see the results by running the following queries:

 SELECT * FROM InventoryCheck SELECT * FROM MissingProductID SELECT * FROM InventoryWarning 

The InventoryCheck table should have one row for each ProductID that was generated. This row was entered at the Check Inventory Level OnPreExecute event, in other words, before the task actually executed. The MissingProductID table should have several rows, one for each ProductID that caused the usp_GetReorderQuantity to raise an error. These rows were added at the Check Inventory Level OnError event. Finally, the InventoryWarning table will have some rows if the inventory level of any of the products was low. These rows were added at the Control Flow level.

Event Bubbling

Events can "bubble" or travel up from child task to parent container. To demonstrate this, you'll move the OnError event handler from the task to a parent container. Using the package created in the previous section, navigate to the Check Inventory Level OnError event handler. Select the Insert Missing Product ID task, right-click, and select Copy from the pop-up window. Create an OnError Event Handler for the Inventory Check container. Right-click the design area of the new event handler and select Paste. Go back to the Check Inventory Level OnError event and click the Delete button to completely remove the original Event Handler.

Run your package again. You will see that the errors are now trapped at the Inventory Check container level by viewing the error handler as the package runs. The OnError event bubbled up from the task to the For Loop container to the Inventory Check container.

What would happen if you had an OnError event handler on both the Check Inventory Level task and the Sequence container? Surprisingly, both will fire when an error is raised at the Check Inventory Level task. This could cause some unexpected results. For example, suppose you had an error handler at the parent container to perform a particular task, such as sending an e-mail message. An error in a child container that you expected to be handled at that level would also cause the parent's OnError Handler to execute. To prevent this from happening, you can set a system variable, Propagate, to False at the child task's Error Handler level.

To demonstrate this, add the OnError Event Handler back to the Check Inventory Level task. Once again, create an Event Handler for the Check Inventory Level OnError Event. You can copy and paste the Insert Missing Product ID task from the Inventory Check OnError event handler. While still working in the Check Inventory Level OnError Event design area, click the design area and open the Variables window. If the system variables are not visible, click the gray box X to display them (see Figure 13-14). Make sure that the Propagate property is set to True, the default.

Figure 13-14

Run the package. While the package is running, navigate to each of the error handlers to watch as they execute. You will notice that both OnError events will fire and the MissingProductID table will end up with two rows for every invalid ProductID. After execution of the package is complete, change the Propagate property to False. Now only the Check Inventory Level OnError event handler will execute. The OnError event will no longer bubble to the parent containers. Run the package again. This time, you should find the expected behavior; the error will be handled only at the Check Inventory Level task.

Note

When the Propagate property is set to False on an OnEvent handler, you no longer need to modify the MaximumErrorCount property of the parent containers from the default setting of 1 to keep the package running after the error.