Chapter 48

Using Event Handlers

Although the main tasks of SSIS packages exist in either the Control Flow or the Data Flow, the Event Handlers tab, the subject of this lesson, is another tab that is very useful when you are creating a package. Event handlers enable you to call tasks when events like errors or warnings occur during the execution of a package, which can be helpful in logging errors or sending notifications. The Event Handlers tab is used to call a task when an event has occurred in the package. The tasks in the event handler run only if the proper event is called in the package. Therefore, it is possible to have tasks in the event handlers of a package that don’t run during the execution of the package because the proper event is not called.

Several events can occur in a package to cause an event to fire and call an event handler:

  • OnError
  • OnExecStatusChanged
  • OnInformation
  • OnPostExecute
  • OnPostValidate
  • OnPreExecute
  • OnPreValidate
  • OnProgress
  • OnQueryCancel
  • OnTaskFailed
  • OnVariableValueChanged
  • OnWarning

The most commonly used events are OnError, OnWarning, OnPreExecute, and OnPostExecute.

  • The OnError event fires when an error occurs during the execution of the selected executable.
  • The OnWarning event fires when a warning occurs during the execution of the selected executable.
  • The OnPreExecute event fires just before the selected executable starts executing.
  • The OnPostExecute event fires just after the selected executable finishes executing.

Creating Event Handlers

When you open the Event Handlers tab for the first time, it looks similar to Figure 48-1. Notice there is a blue link in the middle. When clicked, this link creates an event handler on the package. At the top of the Event Handlers tab are two drop-down menus. The left drop-down menu contains a list of all the executables in the package. This is a list of all the tasks in the package. If no tasks have been created in the package, the only executable listed is the package.

Figure 48-1

c48f001.tif

When you are creating an event handler, it is important to select the proper executable from the drop-down menu to ensure the tasks in the event handler execute when intended.

The second drop-down menu, on the top right of the Event Handlers tab, contains a list of all the events that can be chosen for the selected executable.

Figure 48-2

c48f002.tif

In Figure 48-2, there is a Script Task in the Control Flow of the package and a Data Flow with a source and destination. Notice the source and destination do not show in the drop-down menu of executables. Rather, the entire Data Flow is the executable. Placing an OnPostExecute Event Handler on the Data Flow means the task in the event handler will fire after the entire Data Flow finishes executing. Placing an OnError Event Handler on the Script Task means the tasks in the event handler will fire only if an error occurs on the Script Task. So, for example, if no error occurs or if an error occurs in the Data Flow (as opposed to in the Script Task), the OnError event on the Script Task will not fire.

When you first open the Event Handlers tab and click the blue link in the middle of the tab, it creates an OnError event for the package. This causes the tasks in the event handler to execute if any errors occur during any tasks in the package. Sometimes you may want the tasks in the event handlers to fire only for a certain task in the package. To do this, first select the task in the left drop-down menu and then click the blue link in the event handler tab. This action creates an event handler for the specific tasks and executes only when the proper event occurs on the selected tasks.

Common Uses for Event Handlers

Two of the most common uses for the event handlers are notification and logging. When you want to be notified via e-mail that an error has occurred, the event handlers are the right place to execute this task. Simply place a Send Mail Task in the OnError Event Handler for the package. When you want to be notified via e-mail that a package has completed, again the event handlers are the right place to execute this task. Just place a Send Mail Task in the OnPostExecute Event Handler for the package.

These Send Mail Tasks can contain information about the package. You can include any system variables in the message to tell you what occurred in the package and when.

Another useful purpose of the event handlers is logging. You can create your own custom logging framework if you are not satisfied with the built-in logging of SSIS. By creating Execute SQL Tasks in the event handlers, you can write information from the package to a database. These Execute SQL Tasks execute only when the proper event occurs, enabling you to log errors when they occur with the OnError Event Handler and log warnings when they occur with the OnWarning Event Handler.

After several event handlers have been created and these event handlers are on different executables, you might find it hard to keep track of which executable has an event handler and which does not. The Executable drop-down menu shows all of the event handlers on the package and each executable. There is also an Event Handler folder under the package and each executable. Clicking the plus sign next to this folder opens the folder and shows the event handlers in each of them.

Under the event handler is an Executables folder showing each task in the event handler. In Figure 48-3, you can see the package has three event handlers, each with an Execute SQL Task. The Data Flow and Script Task have OnError Event Handlers with Send Mail Tasks in them. You can also see this information in the Package Explorer tab.

Figure 48-3

c48f003.tif

When you click the plus sign next to any task in an event handler, you see there is another Event Handler folder. The task in the event handler can have an event handler of its own. So, you can have a Send Mail Task in the OnPostExecute Event Handler of a package and then have an Execute SQL Task in the OnError Event Handler of the Send Mail Task. If the Send Mail Task has an error, the error can then be logged with the Execute SQL Task.

Try It

In this Try It, you create a package with an Execute SQL Task with an OnError Event Handler. The event handler is going to cause a pop-up message from a Script Task to show the error. After this lesson, you will understand how to create and use event handlers on a package.

The completed package for this lesson is available for download on the book’s website at www.wrox.com.

Lesson Requirements

Create a package with an Execute SQL Task. Run it with no error, and run it with an error. The OnError Event Handler should fire when an error occurs.

Hints

  • Create an Execute SQL Task in the Control Flow.
  • Create an OnError Event Handler.
  • Create a Script Task in the event handler.

Step-by-Step

1. Create a new package.
2. Drag in an Execute SQL Task.
3. Set the Connection to AdventureWorks2012.
4. Set the SQL statement to Select 1. When you are ready to cause an error, change the SQL to Select a.
5. Click the Event Handlers tab.
6. Select the package in the Executables menu.
7. Select the OnError Event Handler in the Event Handler menu.
8. Click the blue link to create the OnError Event Handler.
9. Drag a Script Task into the event handler.
10. Double-click the Script Task.
11. Set the Script Language to VB.
12. Click the ReadOnlyVariables ellipsis.
13. Place a check next to System::ErrorDescription, and then click OK.
14. Click the Edit Script button.
15. In the Main class under the words, Enter Code Here, type in: 
MsgBox(Dts.Variables(“ErrorDescription“).Value)
16. Close the Script Editor.
17. Click OK in the Script Task Editor.
18. Click the Control Flow tab.
19. Click the green debug arrow on the toolbar to run the package.
20. The Execute SQL Task should have a green check appear in the top right, and no error should occur.
21. Click the stop debugging button on the toolbar.
22. Change the query in the Execute SQL Task to Select a.
23. Click the green debug arrow on the toolbar to run the package.
24. A pop-up message appears matching Figure 48-4.
25. Click OK in the message box and stop the debugging.

Figure 48-4

c48f004.tif
Please select Lesson 48 on the DVD, or online at www.wrox.com/go/ssis2012video, to view the video that accompanies this lesson.
..................Content has been hidden....................

You can't read the all page of ebook, please click here login for view all page.
Reset