Posts tagged ‘Event Handlers’

What’s Happening on SQL Server When My Package Fails?

We encountered an issue recently in an ETL process during the extract portion. When extracting from one very large table, using a NOLOCK hint, we were encountering errors indicating that the data was changing while we were extracting it. Since no other processes were supposed to be using the table during the same time window as the ETL, this was a source of some confusion. Since there was nothing indicating what, if anything, was changing the data, we needed a quick way to see what was going on in the database at the point the package failed.

Normally, when troubleshooting database activity problems, I’d just run sp_who or sp_who2. However, since the problem was occurring between 2 and 3 am, nobody was terribly excited around getting up to monitor the server. We didn’t really want to leave Profiler running all night either. So, we set up the package to log the current database activity whenever an error occurred. Basically, in the OnError event handler, we just added a data flow. The data flow runs the sp_who stored procedure to get the current activity on the database, and saves it to a text file.

This was pretty simple, and I’ve created a sample package to show it. The main control flow has a script task that generates an error, purely for the purpose of firing the OnError event handler.

image

The event handler does a check to make sure this is the first time it’s fired, since we didn’t want to log the activity multiple times. It then runs a data flow, and once it completes, sets a flag to ensure it doesn’t doesn’t fire again.

image

The data flow runs sp_who. While sp_who2 gives better information, it doesn’t provide metadata in an easily consumable format for SSIS. sp_who, on the other hand, works right off, so we went with it for simplicity. The output is sent to a text file.

image

It was pretty simple to implement, and it served the purpose. By reviewing the information logged, we could tell what other processes were running, and what computer it originated from. If you ever encounter a similar situation, hopefully this can help. The template package is located on my SkyDrive.

SSIS Event Handlers

There was a question recently by a fellow MVP, who was wondering if the event handlers in SSIS were fired synchronously or asynchronously. The answer (which I double-checked with Matt Masson) is that they are synchronous. When events are fired asynchronously, they are queued up, and the system runs them as it gets to them. The main thread of the program may continue executing while the events are firing. However, when they are run synchronously, the main thread is paused while the event is executed. Events are not queued up, they are executed as soon as they fired.

What does this mean for your SSIS packages? Well, for the most part it reduces potential issues that can be caused by events firing out of sequence. There can also be threading issues with asynchronous events. So generally using synchronous events makes the programming model a little simpler. It also means that any work done in the event handler will be complete prior to the main thread picking back up. For an example, take the following package, which performs some setup prior to running a data flow. Assuming the setup runs correctly, the data flow should be run.

image

The Perform Setup Work has an OnPostExecute event handler that verifies that setup was performed properly, and sets the value of a variable to 1 or 0, depending on whether it was successful or not.

image

The expression on the precedence constraint checks the value of the variable, to determine whether the data flow should be executed.

image

Since the event handler is executed synchronously, you can count on any work that is done in the event handler being complete before execution moves on from that task. That includes the setting of any variables in the package, which makes the above approach possible. If the event handlers were run asynchronously, you would not be able to count on the variable being set before the expression on the precedence constraint was checked.

However, because your main package execution will be paused while event handlers are firing, you’ll want to make sure that any code in the event handlers runs quickly. You can’t use the event handlers to queue up long running processes, as you can in some languages.

Event handlers are a very powerful, but often underutilized, feature in SSIS. Hopefully this additional information will make them a little easier to use.