Click here to monitor SSC
  • Av rating:
  • Total votes: 138
  • Total comments: 11
Robert Sheldon

SSIS Event Handlers Basics

26 April 2011

SSIS event handlers are the simplest means of turning an SSIS script into a reliable system that is auditable, reacts appropriately to error conditions, reports progress and allows instrumentation and monitoring your SSIS packages. They are easy to implement, and provide a great deal of flexibility. Rob Sheldon once again provides the easy, clear introduction.

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:

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:

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:

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:

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.

Robert Sheldon

Author profile:

After being dropped 35 feet from a helicopter and spending the next year recovering, Robert Sheldon left the Colorado Rockies and emergency rescue work to pursue safer and less painful interests—thus his entry into the world of technology. He is now a technical consultant and the author of numerous books, articles, and training material related to Microsoft Windows, various relational database management systems, and business intelligence design and implementation. He has also written news stories, feature articles, restaurant reviews, legal summaries, and the novel 'Dancing the River Lightly'. You can find more information at http://www.rhsheldon.com.

Search for other articles by Robert Sheldon

Rate this article:   Avg rating: from a total of 138 votes.


Poor

OK

Good

Great

Must read
Have Your Say
Do you have an opinion on this article? Then add your comment below:
You must be logged in to post to this forum

Click here to log in.


Subject: good one
Posted by: Anonymous (not signed in)
Posted on: Friday, April 29, 2011 at 4:28 AM
Message: nice article Robert....Thanks.

Subject: Great job
Posted by: Anonymous (not signed in)
Posted on: Friday, April 29, 2011 at 7:37 AM
Message: Nice

Subject: Thank you
Posted by: Dexter Jones (not signed in)
Posted on: Friday, April 29, 2011 at 9:14 AM
Message: Good coverage of an often-overlooked side of SSIS.

Subject: Great Article
Posted by: Anonymous (not signed in)
Posted on: Friday, April 29, 2011 at 2:23 PM
Message: Thanks,
Thomas
TheSmilingDBA

Subject: Nice one...
Posted by: umajegan (view profile)
Posted on: Monday, May 02, 2011 at 12:22 AM
Message: Thanks Thomas..please post a article related to deployment.

Subject: Good
Posted by: Guru (view profile)
Posted on: Tuesday, May 03, 2011 at 11:25 PM
Message: This is neat and clear. It would have been great, if you touched on event bubbling in case of nested controls, creating events from script components.

Subject: Clear coverage of an often overlooked SSIS capability
Posted by: David Lean (not signed in)
Posted on: Wednesday, May 04, 2011 at 7:47 AM
Message: Nice choice for a article. I will post a pointer to it from my site.
Thx

Subject: Clear coverage of an often overlooked SSIS capability
Posted by: David Lean (not signed in)
Posted on: Sunday, May 08, 2011 at 5:34 AM
Message: Nice choice for a article. I will post a pointer to it from my site.
Thx

Subject: Good info
Posted by: Very nice site! (not signed in)
Posted on: Monday, May 09, 2011 at 10:32 AM
Message: Very nice site! Good article

Subject: Great article and screenshots
Posted by: hurleystylee (view profile)
Posted on: Friday, May 27, 2011 at 6:50 AM
Message: Thanks for the great article! I have a hard time finding SSIS articles that are clear, concise, and have helpful screenshots like this. SSIS is very powerful, but I also think it's not extremely intuitive sometimes.

Subject: One Question Please
Posted by: Toqeer (view profile)
Posted on: Sunday, July 31, 2011 at 2:14 AM
Message: I am having problems importing a file containing a column of character data type. Let me elaborate it. It works fine when there were 2 columns in the excel file employeeid,managerid. When i added another column employeename, it started creating problems. I changed even type of the columns but the problem is still there i.e. set the source and destination column types to unicode for both. In a text file case i was successful. Any help would be appreciated?

 

Phil Factor
Searching for Strings in SQL Server Databases

Sometimes, you just want to do a search in a SQL Server database as if you were using a search engine like Google.... Read more...

 View the blog

Top Rated

SQL Server XML Questions You Were Too Shy To Ask
 Sometimes, XML seems a bewildering convention that offers solutions to problems that the average... Read more...

Continuous Delivery and the Database
 Continuous Delivery is fairly generally understood to be an effective way of tackling the problems of... Read more...

The SQL Server Sqlio Utility
 If, before deployment, you need to push the limits of your disk subsystem in order to determine whether... Read more...

The PoSh DBA - Reading and Filtering Errors
 DBAs regularly need to keep an eye on the error logs of all their SQL Servers, and the event logs of... Read more...

MySQL Compare: The Manual That Time Forgot, Part 1
 Although SQL Compare, for SQL Server, is one of Red Gate's best-known products, there are also 'sister'... Read more...

Most Viewed

Beginning SQL Server 2005 Reporting Services Part 1
 Steve Joubert begins an in-depth tour of SQL Server 2005 Reporting Services with a step-by-step guide... Read more...

Ten Common Database Design Mistakes
 If database design is done right, then the development, deployment and subsequent performance in... Read more...

SQL Server Index Basics
 Given the fundamental importance of indexes in databases, it always comes as a surprise how often the... Read more...

Reading and Writing Files in SQL Server using T-SQL
 SQL Server provides several "standard" techniques by which to read and write to files but, just... Read more...

Concatenating Row Values in Transact-SQL
 It is an interesting problem in Transact SQL, for which there are a number of solutions and... Read more...

Why Join

Over 400,000 Microsoft professionals subscribe to the Simple-Talk technical journal. Join today, it's fast, simple, free and secure.