locked
How to get restartability without using Checkpoints? RRS feed

  • Question

  • Hi All,

    It takes too long to re-execute our SSIS 2012 packages when they fail several hours into execution.  I had problems with Checkpoints when I tried it years ago, so I'm wondering if there's some other way to support re-executing the package by starting at the Task immediately after the last successfully completed Task.

    Any ideas or suggestions?

    Thanks,
    Eric B.


    Monday, July 9, 2018 9:29 PM

Answers

  • Hi All,

    It takes too long to re-execute our SSIS 2012 packages when they fail several hours into execution.  I had problems with Checkpoints when I tried it years ago, so I'm wondering if there's some other way to support re-executing the package by starting at the Task immediately after the last successfully completed Task.

    Any ideas or suggestions?

    Thanks,
    Eric B.


    Its possible

    But you need to develop a framework for that including

    1. A control table to capture the task level execution result (success/failure) for each package

    2. Enable Event handlers for  OnError to capture error info for the tasks, set Propagate variable to False to avoid error from propagating to package level

    3. Using a parallel control flow to check and determine which task to start execution of package in case previous run was failure etc

    So its not a generic solution but you've to develop it specific to your package

    Also if the package has multiple execution paths it will make it more complicated as well


    Please Mark This As Answer if it solved your issue
    Please Vote This As Helpful if it helps to solve your issue
    Visakh
    ----------------------------
    My Wiki User Page
    My MSDN Page
    My Personal Blog
    My Facebook Page

    • Proposed as answer by Russ Loski Tuesday, July 10, 2018 2:11 PM
    • Marked as answer by SQL Server dude Tuesday, July 10, 2018 3:32 PM
    Tuesday, July 10, 2018 10:21 AM
  • In addition to what Visakh16 mentioned, you also have to include provision for cleaning of partially loaded tables or ignoring the rows that have already been loaded correctly from a previously failed task. This may require row level checks and adds to the complexity. Sometimes its faster to just remove all the rows from a failed task and reload afresh instead of checking for their existence/correctness. This also applies in case of checkpoint. Just my 2 cents.

    HTH,
    Cheers!!
    Ashish
    Please mark it as Answered if it answered your question or mark it as Helpful if it helped you solve your problem.

    Tuesday, July 10, 2018 2:16 PM

All replies

  • Hi Eric B.

    -->>I had problems with Checkpoints when I tried it years ago

    What's the problem with the CheckPoints when you tried it?

    As far as I known, it's not possible to restart the same package from the point of failure except using checkpoint in SSIS.

    Alternatively, you need to split the package into multiple packages to isolate the problematic component.

    Regards,

    Pirlo Zhang 


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Tuesday, July 10, 2018 2:20 AM
  • Hi All,

    It takes too long to re-execute our SSIS 2012 packages when they fail several hours into execution.  I had problems with Checkpoints when I tried it years ago, so I'm wondering if there's some other way to support re-executing the package by starting at the Task immediately after the last successfully completed Task.

    Any ideas or suggestions?

    Thanks,
    Eric B.


    Its possible

    But you need to develop a framework for that including

    1. A control table to capture the task level execution result (success/failure) for each package

    2. Enable Event handlers for  OnError to capture error info for the tasks, set Propagate variable to False to avoid error from propagating to package level

    3. Using a parallel control flow to check and determine which task to start execution of package in case previous run was failure etc

    So its not a generic solution but you've to develop it specific to your package

    Also if the package has multiple execution paths it will make it more complicated as well


    Please Mark This As Answer if it solved your issue
    Please Vote This As Helpful if it helps to solve your issue
    Visakh
    ----------------------------
    My Wiki User Page
    My MSDN Page
    My Personal Blog
    My Facebook Page

    • Proposed as answer by Russ Loski Tuesday, July 10, 2018 2:11 PM
    • Marked as answer by SQL Server dude Tuesday, July 10, 2018 3:32 PM
    Tuesday, July 10, 2018 10:21 AM
  • In addition to what Visakh16 mentioned, you also have to include provision for cleaning of partially loaded tables or ignoring the rows that have already been loaded correctly from a previously failed task. This may require row level checks and adds to the complexity. Sometimes its faster to just remove all the rows from a failed task and reload afresh instead of checking for their existence/correctness. This also applies in case of checkpoint. Just my 2 cents.

    HTH,
    Cheers!!
    Ashish
    Please mark it as Answered if it answered your question or mark it as Helpful if it helped you solve your problem.

    Tuesday, July 10, 2018 2:16 PM
  • Thank you, visakh16 and Ashish, that is exactly the type of answer I was seeking.  This gives me a very good idea of the kind of work necessary to employ such a solution.
    Tuesday, July 10, 2018 3:34 PM
  • Thank you, visakh16 and Ashish, that is exactly the type of answer I was seeking.  This gives me a very good idea of the kind of work necessary to employ such a solution.
    And just to further clarify, the table cleaning etc logic to be implemented on failure should be included within the OnError event handler for the corresponding tasks.

    Please Mark This As Answer if it solved your issue
    Please Vote This As Helpful if it helps to solve your issue
    Visakh
    ----------------------------
    My Wiki User Page
    My MSDN Page
    My Personal Blog
    My Facebook Page

    Tuesday, July 10, 2018 3:40 PM