Event Handling - SSIS

 

Each task and container raises events as it runs, such as an OnError event, among several others that are discussed shortly. SSIS enables you to trap and handle these events by setting up workflows that will run when particular events fire. This functionality in SSIS is called event handlers.

You can set up event handlers by navigating to the Event Handlers tab in the SSIS package design environment (see below screenshot). 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.

After adding several event handlers to a package, the workflow can become very complicated, which could be difficult to understand if you had to view it all at once, so separating event handlers from the Control Flow makes sense.

NOTE Make sure your packages are well designed and documented because an event handler that was set up and then forgotten can be the source of a hard-to-troubleshoot problem within the package.

As shown in the above screenshot, the event handler functionality is driven by two dropdown menus. The first, Executable, is used to set the task or container in the package with which the event handler is associated. The highest-level executable is the package itself, followed by the hierarchical list of tasks and containers contained in the Control Flow.

The second dropdown, called Event handler, defines what event the event handler will listen for in the defined executable. Events are described next.

Events

As the package and each task or container execute, 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 are all covered later in the Error and Event Handling Topic.

The following table describes the events available in SSIS at the package level:

In addition to the events fired at the package level, some tasks fire events as well. The following table lists the tasks that have custom events.

Using Event Handlers

Now that you know what each event is and when you may be interested in each event, you need to know how to work with them. As you learned earlier, event handlers provide the means to interact with events. This interaction can be used to log information, perform additional steps, or even pass information to other package items.

The following example demonstrates how to use event handlers by simulating a scenario of checking the inventory status of some random products from AdventureWorks. For this example, you set up a new SSIS package that performs several steps, you define an OnError event handler event to fire when an error occurs, and when you use the OnPreExecute event to capture execution details of the package.

2. Next, you can put the SSIS package together. In a new package, add an OLE DB Connection Manager pointing to the AdventureWorks database. Because this example uses an Execute SQL Task with parameters, you need to be particular about which provider you use. 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.
3. 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.)

4. 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 package’s workflow. In this case, you use it to experiment with the event handlers.
5. et the MaximumErrorCount property of Inventory Check to 9999 in the Property window. This example raises errors by design, and setting the MaximumErrorCount property will allow the simulation to continue running after the errors fire.
6. Drag an Execute SQL Task into the Inventory Check Container, and name it Empty Tables.
7. Double-click the task to open the Execute SQL Task Editor. Ensure that the ConnectionType property is set to OLE DB, and set the Connection property to the AdventureWorks Connection Manager.
8. Click the ellipsis button next to the SQLStatement property and type the following into the Enter SQL Query window to complete the Execute SQL Task configuration:DELETE FROM MissingProductID
DELETE FROM InventoryWarning
DELETE FROM InventoryCheck
9. Drag a For Loop Container into the Inventory Check Container and name it Inventory Query Simulator.
10. Double-click the Inventory Query Simulator and fill in the properties as shown in the following table:

11. Click OK to accept the configuration, and complete the container’s setup by changing the MaximumErrorCount property of the Inventory Query Simulator to 9999 in the Properties window.
12. Drag a precedence constraint from the Empty Tables Task to the Inventory Query Simulator, and add a Script Task to the Inventory Query Simulator Container named Generate ProductID.
13. Double-click the Script Task to open the Script Task Editor.
14. Select the Script pane, setting the ReadWriteVariables property to User::ProductID, as shown in below screenshot.

15. Check the ScriptLanguage property. If this property is set to Microsoft Visual C# 2012 or 2013, change the dropdown to Microsoft Visual Basic 2012 or 2013, based on what version of Visual Studio you’re using the edit packages.
16. Click Edit Script to open the Visual Studio Tools for an Applications design environment. You will use this Script Task to generate a random product identifier. 
17. Replace Public Sub Main with the following code:
Public Sub Main()
Randomize()
Dts.Variables(“User::ProductID”).Value = _
CInt(Int((900 * Rnd()) + 1))
Dts.TaskResult = ScriptResults.Success
End Sub
18. Close the Visual Studio script design environment and then click OK to accept the changes to the Script Task.
19. After setting up the script to get the inventory level, you need to check that value. Do this by adding an Execute SQL Task named Check Inventory Level to the Inventory Query Simulator.
20. Drag a Precedence Constraint from Generate ProductID to Check Inventory Level.
21. Double-click the Check Inventory Level Task to open the Execute SQL Task Editor. 
22. Find the Connection Manager for the AdventureWorks database in the list of connections and change the SQLStatement property to usp_GetReorderQuantity. This task will call the usp_GetReorderQuantity with two parameters, set on the Parameter Mapping window: ProductID and ReorderQuantity as described in the following table. The ResultSet property should be set to None because 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 now look like below screenshot.

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

24. 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.
25. 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.
26. Double-click the precedence constraint and set the Evaluation operation property to Expression and Constraint. The Expression property should be @ReorderQuantity > 0 and the Value property should be left at Success, as shown in below screenshot

27. Once configured, click OK to accept the changes to the precedence constraint.
28. Next, you will set up the Warning task. Double-click the Insert Warning Task and set the ConnectionType to OLE DB. Choose the AdventureWorks Connection Manager from the Connection list, and click the ellipsis next to SQLStatement to add the following query to the Enter SQL Query dialog:
INSERT INTO InventoryWarning (ProductID, ReorderQuantity)
SELECT ?, ? OUTPUT
29. 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.

30. Click OK to accept the configuration. The package should now resemble below screen shot.

When you run the package, sometimes the Check Inventory Level Task will fail. The Generate ProductID script will not always result in 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.

Notice that when the Check Inventory Level Task fails, it will be marked with a red circle with an “X”; but the simulation will continue running and the loop will cause the icon to change between the red circle and a green circle with a checkmark. To view what is going on as the package runs, click the Progress tab. This is also a fantastic troubleshooting tool, providing 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.

 

Check Out SSIS Tutorials

 

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 the following query in SQL Server Management Studio:

SELECT * FROM InventoryWarning

Using the OnError Event Handler Event

The package you just created 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 Task 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.

1. 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 dropdown lists. Clicking the Executable dropdown enables you to see the package objects in a hierarchy. An example is shown in below screenshot, where the package has a child, Inventory Check, which has children Empty Tables, Inventory Query Simulator, and so on.

2. Select Check Inventory Level and click OK to close the list.
3. Choose OnError in the Event Handler list if it isn’t there by default. You must click the link “Click here to create an ‘OnError’ 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 needed. All functionality available at the Control Flow level is available at the Event Handler level, including the capability to add an event handler to an event handler.
4. Drag an Execute SQL Task to the Event Handler design area and name it Insert Missing ProductID.
5. In the properties of the task, set the Connection Type to OLE DB and select the AdventureWorks Connection Manager.
6. Enter the following statement into the Enter SQL Query window under the SQLStatement property:
 INSERT INTO MissingProductID (ProductID) SELECT ?
7. Click OK to accept the query and then switch to the Parameter Mapping page.
8. Add one parameter with the properties shown in the following table:

9. 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 the following query in SQL Server Management Studio:

SELECT * from MissingProductID

Using the OnPreExecute Event Handler Event

Suppose you want to keep a record of all the ProductID numbers that were tested. You can do this using the OnPreExecute event handler. To do so, follow these steps:

 1. Working from the previous package, add another event handler to the Check Inventory Level Task. With Check Inventory Level selected in the Executable list, select OnPreExecute under Event Handler.
2. Click the link to create the handler, and add an Execute SQL Task to the Event Handler design area and name it Record ProductID. 
The Execute SQL Task Editor will store the ProductID numbers.
3. Double-click the Execute SQL Task to open the Execute SQL Task Editor. Use the OLE DB AdventureWorks Connection Manager, and add this statement to the SQLStatement property by typing in the property text box or using the Enter SQL Query dialog:

INSERT INTO InventoryCheck (ProductID) SELECT ?
4. Add one parameter on the Parameter Mapping pane with exactly the same properties as the one added to the OnError event task, as the following table shows.

5.Click OK to accept the configuration and run the package.
6. 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 stored procedure 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 Handler Inheritance

Events handlers will inherit the events of their children. This means that if you have a container, and the container has an event handler OnError event defined on it, then if a child task that exists in the container errors, the event handler of the container will fire. This is sometimes referred to as the event “bubbling” or travelling up from child task to parent container. As mentioned already, the highest level executable is the package itself. Therefore, if you define an event handler event at the package level, then whenever that event occurs in the package, the event handler will fire.

Frequently Asked SSIS Interview Questions & Answers

 

The same inheritance occurs if a parent package executes a child package using the Execute Package Task. It will appear as though duplicated events are fired, but it is simply the event handler for both the child package and the parent package handling the event. You can prevent this phenomenon by setting the Propagate variable to false in the child package.

To demonstrate this with the example inventory package, you’ll move the OnError event handler from the task to a parent container:

1. Using the package created in the previous section, navigate to the Check Inventory Level OnError event handler.
2. Copy the Insert Missing ProductID Task and paste it into a new OnError event handler for the Inventory Check Container.
3. You can delete the original event handler and run your package again. You can 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, follow the steps:

1. Add the OnError event handler back to the Check Inventory Level Task. 
2. Once again, create an event handler for the Check Inventory Level OnError event. You can copy and paste the Insert Missing ProductID Task from the Inventory Check OnError event handler.
3. While still working in the Check Inventory Level OnError Event design area, click the design area and open the Variables window.
4. If the system variables are not visible, click the Grid Options icon and check the Show system variables property. As shown in Figure 18-18, make sure that the Propagate property is set to True, the default.

5.Run the package. While it is running, navigate to each of the error handlers to watch as they execute. Notice that both OnError events will fire and the MissingProductID table ends up with two rows for every invalid ProductID.
6 .After execution of the package is complete, change the Propagate property to False (use the Variables window). Now only the Check Inventory Level OnError event handler will execute. The OnError event will no longer bubble to the parent containers. 
7. Run the package again. This time, you should find the expected behaviour; the error is 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.

 

List of Related Microsoft Certification Courses:

SSRS	Power BI
SSAS	SQL Server
SCCM	SQL Server DBA
SharePoint	BizTalk Server
Team Foundation Server	BizTalk Server Administrator