Much of the information you read about creating SQL Server Integration Services (SSIS) packages focuses on how to develop the control flow and the data flow. As you're no doubt aware, you develop the control flow on the Control Flow tab in SSIS Designer and on the data flow on the Data Flow tab. SSIS Designer also includes the Event Handlers tab, which lets you design event handlers that are based on package executables and the events they generate. In this article, I'll introduce you to the Event Handlers tab and demonstrate how to develop an event handler for a specific executable and one of its events.
To create event handlers for an SSIS package, you must first implement the executables that the package will contain. An executable can be any control flow task or container or it can refer to the package itself. You can create event handlers on any executable in the package.
For the event handler example that I use below, I developed a simple SSIS package that retrieves data from the AdventureWorks sample database and inserts it into the EmpHireDates table and EmpHireDates2 table. I used the following Transact-SQL statements to create these two tables:
SELECT c.FirstName, c.MiddleName,
c.LastName, e.HireDate
INTO HumanResources.EmpHireDates
FROM Person.Contact c
INNER JOIN HumanResources.Employee e
ON c.ContactID = e.ContactID
SELECT * INTO HumanResources.EmpHireDates2
FROM HumanResources.EmpHireDates
Figure 1: The Control Flow tab for this package.
The first Execute SQL task truncates the EmpHireDates and EmpHireDates2 tables. The Data Flow task retrieves data from the Employee and Contact tables and loads it into the EmpHireDates table.
Figure 2: Details of the Data Flow tab.
The second Execute SQL task in the control flow retrieves the data from the EmpHireDates table and loads it into EmpHireDates2 table. The SSIS package shown in Figures 1 and 2 is very simple. However, the package does provide enough elements to demonstrate how to create an event handler. If you were to click on the Event Handlers tab, you would see a design surface similar to the one shown in Figure 3. Two important elements near the top of the design surface are the Executable drop-down list and the Event handler drop-down list.
Figure 3: Executable and event handler drop-down lists.
Step 2: Selecting an executable
The Executable drop-down list displays a hierarchical view of the executables that currently exist in the package. In Figure 4, you'll see that the Package executable is at the top of the list. Beneath the Package executable are the Execute SQL Task and Sequence Container executables. Because the Sequence Container in the control flow contains a Data Flow task and an Execute SQL task, you'll find both of these executables in the Executables folder beneath the Sequence Container executable.
Figure 4: Execute SQL Task and Sequence Container executables.
Beneath each executable, you'll also find an Event Handlers folder. Whenever you define an event handler on an executable, that event handler appears beneath the appropriate Event Handlers folder. To define an event handler on the executable, first select that executable in the drop-down list and then click OK. For this article, I created an event handler on the Sequence Container executable.
Step 3: Selecting an event
For each executable, you can create one or more event handlers. Each event handler must be based on one of the events listed in the event handler drop-down list, shown in Figure 5. For example, if you select the Sequence Container executable, you can create an event handler based on the OnError event, the OnInformation event, the OnPostExecute event or one of the other events in the drop-down list. Most of the event names are self-explanatory, but you can find information about each event type in Microsoft SQL Server 2005 Books Online.
Figure 5: Event handler drop-down list.
When you run the package and subsequently the executable, it generates one or more of the pre-defined events. Which events it generates depends on the success or failure of the operation. For example, if the Sequence Container encounters an error when it's running, it generates an OnError event; or before the executable begins to run, it generates an OnPreExecute event. Sometimes the event is generated by the tasks within the container. For example, the Data Flow task generates OnInformation events.
For this article, I selected the OnInformation event. In other words, I created an OnInformation event handler for the Sequence Container executable. As a result, whenever that container or any of the tasks contained in that container generate an OnInformation event, the OnInformation event handler takes whatever action is defined for that event handler.
Step 4: Developing a workflow
Selecting an executable and an event represents only the first step you take in creating an event handler. In order to be meaningful, an event handler must take some sort of action.
You define this action as a workflow, in the same way you define the control flow. If you refer back to Figure 3, you'll see that the Event Handlers design surface contains only a link. Once you've selected your executable and event, you should click that link to activate the design surface and create the initial event handler. This activation is specific to the executable/event pair for which you're creating the event handler. If you were to select a different executable/event pair, the design surface would once again look like what appears in Figure 3.
After you activate the design surface, add control flow elements to that surface, as shown in Figure 6. In this case, I added an Execute SQL task. As a result, whenever the Sequence Container or one of its tasks generates an OnInformation event, the Execute SQL task will run. You can just as easily add other tasks, such as a Send Mail task or a Message Queue task. And just like the control flow, you can use precedence constraints to connect workflow tasks and enclose tasks in containers, and you can use system and user-defined variables in your containers and tasks.
Figure 6: Adding control flow elements to the design surface.
Step 5: Working with variables
The Variables window in Figure 6 shows how you can use any variable whose scope is available to the selected executable. In this case, I used several system variables to log OnInformation events to the SSIS_events table in SQL Server. To create this table, I used the following Transact-SQL statement:
CREATE TABLE dbo.SSIS_events
(
EIGUID UNIQUEIDENTIFIER NOT NULL,
PackageName NVARCHAR(50) NOT NULL,
SourceName NVARCHAR(50) NOT NULL,
EventInfo NVARCHAR(200) NOT NULL,
TimeLogged DATETIME NOT NULL DEFAULT(getdate())
)
Figure 7: Parameter Mapping page of Execute SQL task editor.
When working with variables in Execute SQL tasks, you must map the variables to the parameter placeholders in the SQL statement used within the tasks. For this example, I mapped four system variables to the parameters. Figure 7 shows the Parameter Mapping page of the Execute SQL task editor. Each mapping is defined as Input and the parameter names are simply integers, beginning with 0.
After you define your parameter mapping, define your SQL statement. I used the following statement in the Execute SQL task:
INSERT INTO dbo.SSIS_events
(EIGUID, PackageName, SourceName, EventInfo)
VALUES (?,?,?,?)
Notice the use of question marks as parameter placeholders. The order of these question marks corresponds with the parameter names defined on the Parameter Mapping page. For example, the second variable is PackageName. The parameter name for that variable is 1, which is actually the second parameter. That means the second question mark corresponds with this variable, so the package name will be inserted into the table as the second value. If you were to run this package, SSIS would log the OnInformation events shown in the following table:
| EIGUID |
PackageName |
SourceName |
EventInfo |
TimeLogged |
| 7359F704-8267-4E6B-8785-1FA2ACB0A1E4 |
Package |
Data Flow Task |
Validation phase is beginning. |
2007-12-26 16:53:37.700 |
| 7359F704-8267-4E6B-8785-1FA2ACB0A1E4 |
Package |
Data Flow Task |
Prepare for Execute phase is beginning. |
2007-12-26 16:53:38.030 |
| 7359F704-8267-4E6B-8785-1FA2ACB0A1E4 |
Package |
Data Flow Task |
Pre-Execute phase is beginning. |
2007-12-26 16:53:40.907 |
| 7359F704-8267-4E6B-8785-1FA2ACB0A1E4 |
Package |
Data Flow Task |
Execute phase is beginning. |
2007-12-26 16:53:42.623 |
| 7359F704-8267-4E6B-8785-1FA2ACB0A1E4 |
Package |
Data Flow Task |
The final commit for the data insertion has started. |
2007-12-26 16:53:43.047 |
| 7359F704-8267-4E6B-8785-1FA2ACB0A1E4 |
Package |
Data Flow Task |
The final commit for the data insertion has ended. |
2007-12-26 16:53:43.110 |
| 7359F704-8267-4E6B-8785-1FA2ACB0A1E4 |
Package |
Data Flow Task |
Post Execute phase is beginning. |
2007-12-26 16:53:43.200 |
| 7359F704-8267-4E6B-8785-1FA2ACB0A1E4 |
Package |
Data Flow Task |
Cleanup phase is beginning. |
2007-12-26 16:53:43.297 |
| 7359F704-8267-4E6B-8785-1FA2ACB0A1E4 |
Package |
Data Flow Task |
"component "OLE DB Destination" (90)" wrote 290 rows. |
2007-12-26 16:53:43.373 |
The Data Flow task generated OnInformation events. The Sequence Container itself did not, nor did the Execute SQL task within that container. Of course, this is an example -- you can log any information you want or you can take any other type of action. In addition, you can create event handlers on other executables. For instance, you might want to send an email if a package fails, or you might want to log error events only on the Data Flow task and nowhere else. Or you can create multiple event handlers on the Data Flow task and not on other executables.
The point is, the Event Handler tab in SQL Server Integration Services is quite flexible and lets you take a variety of actions depending on the events you want to monitor and the actions you want to take. The Event Handlers tab is as flexible and extensible as the Control Flow tab or the Data Flow tab -- you just have to know how to use it.
| ABOUT THE AUTHOR: |
|
Robert Sheldon is a technical consultant and the author of numerous books, articles and training material related to Microsoft Windows, various relational database management systems (including SQL Server), and business intelligence design and implementation. He is also the author of the novel Dancing the River Lightly. You can find more information at www.rhsheldon.com. |
|
 |
|
Rate this Tip
|
To rate tips, you must be a member of SearchSQLServer.com.
Register now
to start rating these tips. Log in if you are already a member.
|
DISCLAIMER: Our Tips Exchange is a forum for you to share technical advice and expertise with your peers and to learn from other enterprise IT professionals. TechTarget provides the infrastructure to facilitate this sharing of information. However, we cannot guarantee the accuracy or validity of the material submitted. You agree that your use of the Ask The Expert services and your reliance on any questions, answers, information or other materials received through this Web site is at your own risk.
|
|
