none
Event Handlers don't execute after restart from checkpoint

    Question

  • Hi,

      I have OnPreExecute and OnPostExecute Event Handlers on the Package level for custom logging.  They fire on pre/post exec for every task in the package as I wish.  They don't fire however when the package is restarted from a checkfile.  FailPackageOnFailure is set to false for the event handlers and every task within them.  I thought this was how to prevent the task from being written to checkfile and in turn prevent the task from failing to execute after restart.

    Is it true there are no workarounds for this behaviour?

    Thanks,

    Josh

    Wednesday, September 01, 2010 10:00 PM

Answers

All replies

  • It's true, I found this out a while ago when trying to do the same thing: http://www.bimonkey.com/2009/06/event-handlers-and-checkpoints/

    The package does literally resume from the point of failure - rather than fire all pre execute events then continue from the failure point.

    Lesson 1: Don't use event handlers for custom logging if using checkpoints

    Lesson 2: Don't use checkpoints. Try and handle failure and restartability using something like a control framework: http://ssisetlframework.codeplex.com/

    Cheers, James


    James Beresford @ www.bimonkey.com
    SSIS / MSBI Consultant in Sydney, Australia
    SSIS ETL Execution Control and Management Framework @ SSIS ETL Framework on Codeplex
    Wednesday, September 01, 2010 11:22 PM
  • Yes, this has always been the case. I believe it to be a bug and submitted it a so: 

    Eventhandlers don't work if a package is started from a checkpoint 

     

    Unfortunately MSFT don't seem to agree because they haven't fixed it yet.

    Like James I would advise to never use checkpoints. They're fiddly to setup and in some circumstances do not work at all (another bug).

     


    http://sqlblog.com/blogs/jamie_thomson/ | @jamiet | About me
    Jamie Thomson
    Thursday, September 02, 2010 5:45 AM
    Moderator
  • Thanks Guys.  Not the answer I was hoping for but at least I can stop banging my head on the wall.

    James,  I saw your article prior to this post, but hoped it wasn't so.  Then I found the following which was no help:

    http://www.sqlservercentral.com/articles/Integration+Services+(SSIS)/65969/

    Perhaps I misunderstood the case that this article was solving, but it didn't change the behaviour of handlers not firing after restart.

    Also, I looked over your control framework, very nice.  Am I correct to say that your framework considers a 'task' to be a package execution.  Rather than the execution of containers within a package?  So a 'Run' is equal to an execution of one or more packages.  Therefore, when a package fails, the package will be re-executed.  But the framework doesn't restart a package beginning with the container that failed as the built-in checkpoint functionality attempts to do.  Hope I'm making sense.  Have I misunderstood your framework?  Can it or can it not restart a failed package from the point of failure?

    Jaime, I'm using your custom logging from the following, (thanks for that by the way):

    http://consultingblogs.emc.com/jamiethomson/archive/2005/06/11/SSIS_3A00_-Custom-Logging-Using-Event-Handlers.aspx

    I'm thinking it may not be too much trouble to build in some restartability using the log data.  Have you done any restartability work based on your logging technique?  Do you have any suggestions for a checkpoint alternative which will allow this logging technique to fire?

    PS..I get 'page not found' on your connect link.

    Thanks again,

    Josh

     

    Thursday, September 02, 2010 4:07 PM
  • Jaime, I'm using your custom logging from the following, (thanks for that by the way):

    http://consultingblogs.emc.com/jamiethomson/archive/2005/06/11/SSIS_3A00_-Custom-Logging-Using-Event-Handlers.aspx

    I'm thinking it may not be too much trouble to build in some restartability using the log data.  Have you done any restartability work based on your logging technique?  Do you have any suggestions for a checkpoint alternative which will allow this logging technique to fire?

    Yeah, I'm big on restartability. I usually introduce the concept of "Load Stages" and I'll have a table ([LoadStage]) that lists them. I'll have a dedicated package that controls each LoadStage and a "master" package that calls all of those. After each successful LoadStage completion the master package will flip a flag in [LoadStage] indicating that it has succeeded.

    The master package needs to interrogate [LoadStage] when it starts exec'ing to find out where it needs to start from.

     

    Something like that anyway...

    PS..I get 'page not found' on your connect link.

    That happens a lot. Connect, as an app, is a PoS (but I can't complain about the fact that it exists)!
    http://sqlblog.com/blogs/jamie_thomson/ | @jamiet | About me
    Jamie Thomson
    Thursday, September 02, 2010 4:20 PM
    Moderator