Since the release of SQL Server 2005, SQL Server Integration Services (SSIS) has proven to be an effective tool for managing extract, load, and transform (ETL) operations. However, most of the material you find about developing SSIS packages focuses on the control flow and data flow as they’re rendered in Business Intelligence Development Studio (BIDS). But another important-and often overlooked-feature in an SSIS package is the event handler.
Event handlers let you run SSIS components on a per-executable, per-event basis. For example, suppose your package includes a Foreach Loop container. You can associate one or more SSIS components with each event generated by that container when it is executed. This includes such events as OnError, OnInformation, and OnPostExecute. The components you associate with the container’s events are separate from the regular control flow. Consequently, you can configure them specifically to the needs of the associated event handler. This will all become clearer as I demonstrate how to configure an event handler.
Note: In SSIS, an executable is any component you add to the control flow, plus the package itself. The components act as child executables to the package. If you add a component to a container, the container is the parent executable (but the child of the package executable), and the component within the container is the child executable.
In this article I explain how to add event handlers to an SSIS package. The package in this case inserts data into two tables I created in the AdventureWorks2008 sample database on a local instance of SQL Server 2008. The first table, People, will be the target of the package after that package extracts data from the Person.Person table in the AdventureWorks2008 database. The second table, RunInfo, will store data that is generated by the event handlers I’ll be adding to the package The following Transact-SQL script includes the CREATE TABLE statements necessary to add both tables:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 |
IF OBJECT_ID('People', 'U') IS NOT NULL DROP TABLE dbo.People; CREATE TABLE dbo.People ( PersonID INT NOT NULL, FirstName NVARCHAR(50) NOT NULL, LastName NVARCHAR(50) NOT NULL, CONSTRAINT PK_People PRIMARY KEY CLUSTERED (PersonID ASC) ); IF OBJECT_ID('RunInfo', 'U') IS NOT NULL DROP TABLE dbo.RunInfo; CREATE TABLE dbo.RunInfo ( RunID INT NOT NULL IDENTITY, TaskID NVARCHAR(50) NOT NULL, TaskName NVARCHAR(50) NOT NULL, TaskTime DATETIME NOT NULL DEFAULT(GETDATE()) CONSTRAINT PK_RunInfo PRIMARY KEY CLUSTERED (RunID ASC) ); |
After I added the two tables to the database, I created the SSIS package. Figure 1 shows the control flow of the package after I added the necessary components. As the figure indicates, the control flow includes an Execute SQL task to truncate the People table and includes a Sequence container that contains two Data Flow tasks.
Figure 1: Configuring the control flow
Notice that Figure 1 also shows the AdventureWorks2008 connection manager, which is an OLE DB connection manager that connects to the AdventureWorks2008 database on the local instance of SQL Server 2008. I use this connection manager for all my connections.
Next I configured the two data flows. The Load Data 1 data flow, shown in Figure 2, uses an OLE DB source to retrieve data from the Person.Person table and a SQL Server destination to insert data into the People table.
Figure 2: Configuring the Load Data 1 data flow
When I configured the OLE DB source, I used the following SELECT statement to retrieve data from the Person.Person table:
|
1 2 3 4 5 6 7 8 |
SELECT BusinessEntityID, FirstName, LastName FROM Person.Person WHERE BusinessEntityID < 10000; |
Notice that I retrieve only those rows whose BusinessEntityID value is less that 10000. The Load Data 2 data flow is identical to Load Data 1, except that I used the following SELECT statement:
|
1 2 3 4 5 6 7 8 |
SELECT BusinessEntityID, FirstName, LastName FROM Person.Person WHERE BusinessEntityID >= 10000; |
As you can see, this time I’m retrieving only those rows whose BusinessEntityID value is greater than or equal to 10000. I set up the two data flows in this way to better demonstrate the relationship between executables and event handlers in the SSIS package. You can download the completed package from the speech-bubble at the head of this article, or you can simply create the package yourself. If you’re uncertain how to create an SSIS package or configure any of these components, be sure to check out SQL Server Books Online. Once you’ve set up your package, you’re ready to add the event handlers.
Selecting an Executable and Event
You configure your event handlers on the Event Handlers tab of SSIS Designer. The tab, shown in Figure 3, provides access to the package’s executables and to the events associated with each executable. The tab also provides the design surface necessary to add components to an event handler, just as you would add components to the control flow.
Figure 3: Accessing the Event Handlers tab
When you first access the Event Handlers tab, the selected executable is the package itself, which in this case, I’ve named EventHandlersPkg. In addition, the selected event is OnError. As a result, any components you would add to the design surface at this point would be specific to this combination of this executable and event pair. To view all the executables, click the down-arrow on the Executable text box and then expand the list of executables, as shown in Figure 4.
Figure 4: Viewing the package executables
Notice that the executables are listed hierarchically, with EventHandlersPkg at the top of the hierarchy and the Execute SQL task (Truncate People table) and the Sequence container (Load People data) at the second level of the hierarchy. At the third level, below the Sequence container, are the two Data Flow tasks (Load Data 1 and Load Data 2). For each executable, a folder named Event Handlers is listed. Any event handlers you configure for an executable are listed in that folder, with the event handlers sorted by event.
Note: The sources, transformations, and destinations you add to a data flow are not executables. They are all part of the Data Flow executable, which is why these components are not included in the list of executables on the Event Handlers tab.
For each executable, you will find a list of events in the Event handler list. The list includes all the events associated with the selected executable. To select an event for an executable, click the down-arrow in the Event handler list, shown in Figure 5, and then select the event.
Figure 5: Viewing the package events
When working on the design surface of the Event Handlers tab, you are always working with a specific executable-event pair. That means, in order to configure an event handler, you must first select an executable and then select the event. For example, if you refer to Figure 6, you’ll see that I’ve selected the Truncate People table executable and then selected the OnError event. As a result, any components I add to this executable-event pair will run whenever the Truncate People table executable generates an OnError event.
Figure 6: Selecting an executable and event handler
Once you’ve selected your executable-event pair, you’re ready to add your components, so let’s take a look at how you do that.
Configuring the Event Handler
If you refer back to Figure 6, you’ll notice that the design surface includes a link that instructs you to click it in order to create an event handler for that specific event and executable. You’ll be presented with this link for any executable-event pair for which an event handler has not been configured. You must click this link to create the event handler and add any components.
Note: Once you click the link on the design surface for a specific executable-event pair, an event handler is created, even if you don’t add a component. To delete an event handler for a specific executable-event pair, click the Delete button to the right of the Event handler text box.
The first event handler we’ll create is for the Load Data 1 executable and the OnPostExecute event. After you select the executable-event pair and click the link, you’re ready to go. For the EventHandlersPkg package, I’ve added an Execute SQL task, as shown in Figure 7. You add the task just as you would add any component to the control flow.
Figure 7: Adding an Execute SQL task to an event handler
When you configure an event handler, you can use any of the system or user variables available to the executable, so let’s look at the variables available to the Load Data 1 executable. To view these variables, open the Variables pane in SSIS Designer by clicking Variables in the SSIS menu. Then, in the Variables pane, click Show System Variables to list all variables available to your event handler components, as shown in Figure 8.
Figure 8: Viewing the variables in the Variables pane
In this case, we want to use the SourceID and SourceName system variables to identify the components that are generating the OnPostExecute events, which is what we’ll log to the RunInfo table. We’ll be adding the variables in the Execute SQL task, so let’s look at how you configure that task. Figure 9 shows the General page of the Execute SQL Task editor.
Figure 9: General tab of the Execute SQL Task Editor
Notice that I’ve specified the AdventureWorks2008 connection manager in the Connection property. Then, in the SQLStatement property, I added the following INSERT statement:
|
1 2 3 |
INSERT INTO RunInfo (TaskID, Taskname) VALUES (?, ?); |
As you can see, I’ve included two question mark placeholders in the VALUES clause. The placeholders allow you to insert the values from the SourceID and SourceName system variables into the RunInfo table. However, to do this, you must also map the variables to the statement. Figure 10 shows the Parameter Mapping page of the Execute SQL Task editor, which includes a listing for each variable that will be used by the INSERT statement.
Figure 10: Parameter Mapping tab of the Execute SQL Task Editor
As Figure 10 indicates, both variables are input variables configured with the NVARCHAR data type. In addition, the name of the first variable (SourceID) is 0, and the name of the second variable (SourceName) is 1. This follows the naming conventions necessary to pass parameter values into the INSERT statement. That’s all there is to configuring the Execute SQL task. And that’s also the only component I added to the Load Data 1 executable. However, I also configured the same event for the Load Data 2 executable and then added an Execute SQL task to the event handler, set up just like the task in Load Data 1. That means when the SSIS package runs, it will execute two event handlers, one for each data flow.
Running the SSIS Package
Once you’ve configured your event handlers, you’re ready to run the SSIS package. Running a package that contains event handlers is no different from running any other type of package. The event handlers will be executed as long as the executable issues the event for which SSIS components have been configured. That means, in this case, as long as the Data Flow tasks run successfully, the OnPostExecute events will be issued and the Execute SQL tasks will run. The INSERT statements within those tasks will then add the variable information to the RunInfo table.
After I ran the EventHandlersPkg package the first time, I queried the RunInfo table and received the following results:
|
RunID |
TaskID |
TaskName |
TaskTime |
|
1 |
{85B4ED54-D20D-4E90-B60C-E0151D7B1348} |
Load Data 1 |
2011-04-17 19:25:13.370 |
|
2 |
{057C0C52-99B1-4B96-BDC8-923A6A85CCBE} |
Load Data 2 |
2011-04-17 19:25:13.670 |
As you can see, the task ID and task names have been added to the table, and as expected, there is one row for each task. If you run the package multiple times, you will see additional rows.
Although the example I’ve demonstrated in this article is very basic, it does show you the power of event handlers to capture a variety of information under specific circumstances. And you can perform other actions as well. For example, in addition to being able to set up a system to audit your packages, you can take such steps as sending an email if an executable issues an OnError event. SSIS event handlers are flexible and provide many options for auditing and monitoring your SSIS packages. And given how easy they are to implement, it is well worth the time and effort to take full advantage of all that event handlers have to offer.
Load comments