none
SSIS Checkpoint restart second time after failure RRS feed

  • Question

  • Hi Experts,

    I am working on checkpoints and had a question about the restart ability of the package from point of failure.

    1. My package has 5 Tasks running sequentially.
    2. Task 2 and Task 4 has SELECT 1/0 intentionally to test failure.
    3. Task 1, 3 and 5 is just SELECT 1

    Below are the steps that I am testing.

    1. Run the package and it fails on Task 2 by creating a checkpoint file.
    2. Correct the Task 2 to SELECT 1
    3. Re-start the package (Uses checkpoint file)
    4. Runs from Task 2 and again fails on Task 4

    Now here are my questions.

    Does checkpoint work if I restart now after failing for the second time ? (Checkpoint file still there as package didn't complete successfully to delete the checkpoint file automatically.)

    I tried this but it gives me an error

    Error: Error loading value "<DTS:VariableValue xmlns:DTS="www.microsoft.com/SqlServer/Dts"/>" from node "DTS:VariableValue"

    Any help is greatly appreciated.


    SQL Killer



    • Edited by SQLKiller Tuesday, February 16, 2016 8:16 PM
    Tuesday, February 16, 2016 8:10 PM

Answers

  • Ok, found the issue.

    There is a variable in my package that is type object. Most of the developers say to avoid object type for checkpoints because of their weird behavior.

    It behaved weird in my case as well for second run it was not writing these variable to checkpoint file properly and hence corrupting the checkpoint file.

    As a workaround I removed object variable and created a string variable instead (was fine in my case). And package started to run successfully on the second failure as well.

    Hope this will help someone out there.

    Thanks


    SQL Killer

    Wednesday, February 17, 2016 4:09 PM

All replies

  • Hi,

    I think when you did #2 you only have one failure expected to experience, at Task 4 and the package should restart from there.

    I just suspect the test wasn't arranged correctly timing wise.


    Arthur

    MyBlog


    Twitter

    Tuesday, February 16, 2016 8:50 PM
    Moderator
  • Thanks Arthur for a response.

    Anyways the trouble I am having is if I use checkpoint again for the second failure it fails with the error given above.

    What is interesting to see is the checkpoint file after the second failure has some missing variables in it. I was surprised to see no database and server variable in the checkpoint file after the second failure.

    Don't know what's going on ...


    SQL Killer

    Tuesday, February 16, 2016 9:15 PM
  • Ok, found the issue.

    There is a variable in my package that is type object. Most of the developers say to avoid object type for checkpoints because of their weird behavior.

    It behaved weird in my case as well for second run it was not writing these variable to checkpoint file properly and hence corrupting the checkpoint file.

    As a workaround I removed object variable and created a string variable instead (was fine in my case). And package started to run successfully on the second failure as well.

    Hope this will help someone out there.

    Thanks


    SQL Killer

    Wednesday, February 17, 2016 4:09 PM
  • Hi,

    Glad to hear that your issue got solved, and thank you for your sharing. Please mark it as answer which will help others to find the correct solution easily.

    Regards,


    Charlie Liao
    TechNet Community Support

    Tuesday, February 23, 2016 3:18 AM
    Moderator