Aaron Akin

January 3, 2009

SSIS Checkpoints with Event Handlers

Filed under: Checkpoints,Event Handlers,SQL 2005,SSIS — Aaron Akin @ 2:33 am

I recently had a project that required my package to utilize checkpoints, which wouldn’t have been a big deal, except this package had event handlers on some of the tasks.

Working with checkpoints never seems to be as straight forward and intuitive as you’d like it to be, and trying to implement them into a package that has event handlers can be quite frustrating (to say the least).

Since Microsoft BOL contains very limited documentation on the advanced uses of checkpoints in SSIS, I created a simple test package and played with as many properties as I could until I finally got the event handlers to work.

By the way, you can probably get around any of these problems by writing your own custom code, but I wanted to find out if it could be accomplished without using any custom logic (i.e. script tasks).

Here’s what I came up with…

Setup the package

For this case, I created a simple package with a Script Task that has OnPreExecute and OnError event handlers.

  1. Create a new package and enabled checkpoints by setting the following properties for the package.
    SSIS Checkpoints Properties
  2. Add a Script Task to the control flow and set the FailPackageOnFailure property on the task to True to enable it as a restart point.
  3. Open the script in the Script Task for editing and add the following code to the first line in Main():
      Public Sub Main()
    *   Throw New DivideByZeroException
     

OnPreExecute Event Handlers

In my OnPreExecute event handler, I have two Script Tasks (Handler Script Task 1 & Handler Script Task 2). Here’s how I was finally able to get this event to fire and execute both of these tasks after the package restarting due to the failure of the Script Task in the main control flow:

  1. Add a For Loop Container to the OnPreExecute event handler workflow.
  2. Create a variable with the scope of the container and use it in the properties of the For Loop Container to create a loop that will only have one iteration.
    SSIS For Loop Properties (One Iteration)
  3. Set the FailPackageOnFailure property on the For Loop Container to True.
  4. Set the FailParentOnFailure property on the two tasks (Handler Script Task 1 and Handler Script Task 2) inside of the For Loop Container to True.

Make sure the checkpoint file gets deleted if it exists, and then execute the package. Restart it after failure and you’ll notice that the tasks in the OnPostExecute event handler are executed every time the package is restarted.

OnError Event Handlers

In my OnError event handler, I copied everything from the OnPreExecute workflow, pasted it into the OnError workflow, and then:

  • Set the ForceExecutionResult property on the last task in the For Loop Container to Failure.
    OR
  • Add a new Script Task to the OnError workflow as the last task within For Loop Container, and then set the ForceExecutionResult property on this task to Failure.

Make sure the checkpoint file gets deleted if it exists, and then execute the package. Restart it after failure and you’ll notice that the tasks in the OnError event handler are executed every time the package is restarted.

Hopefully this will help save others some very frustrating hours of figuring all this stuff out.

-A

Advertisements

4 Comments »

  1. I recently wrote a 3-part series on Checkpoints. The first article was published today on SQLServerCentral.com, and the next 2 should be published in the next couple of weeks. This article will show you some of the basics of checkpoints, including enabling and configuring them in their simplest form. The other two articles will cover more advanced usages of checkpoints. http://www.sqlservercentral.com/articles/SSIS/65807/

    Comment by Aaron Akin — February 17, 2009 @ 9:54 am | Reply

  2. The second article on this subject that I wrote was published today on SQLServerCentral.com. This article will show you how containers can be utilized with checkpoints enabled, as well as the limitations of each of the different types of containers. http://www.sqlservercentral.com/articles/SSIS/65809/

    Comment by Aaron Akin — February 24, 2009 @ 7:20 am | Reply

  3. The third article on this subject that I wrote was published today on SQLServerCentral.com. This article will show you some tips and tricks to using event handlers when you have checkpoints enabled. http://www.sqlservercentral.com/articles/SSIS/65969/

    Comment by Aaron Akin — March 10, 2009 @ 6:29 am | Reply

  4. you are awesome ..thats a great workaround

    Comment by sriki — September 16, 2013 @ 2:02 pm | Reply


RSS feed for comments on this post. TrackBack URI

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Create a free website or blog at WordPress.com.

%d bloggers like this: