Post reply

SSIS Transaction and checkponts in SQL server 2008 R2

  • September 13, 2012 at 8:24 am

    #75662

    SSIS Packages Containers Checkpoints and Transactions.

    Hi evryone, I have a SQL server 2008 R2 on a windows 2008 server.

    I have made all the configuration described in the article:

    http://blogs.technet.com/b/meacoex/archive/2011/03/20/bringing-the-transaction-mode-with-ssis-into-reality.aspx

    but the package doesn't work as expected.

    My example has also transactions in it: I have three sequence containers in cascade on success.

    Each sequence container implements a transaction with two task:

    1) Delete table A in a sql DB

    2) copy data from a tab separated text file into table A.

    If I change a value in a records in the text file in order to cause a PRIMARY KEY CONSTRAINT VIOLATION ERROR the package fails and a Checkpoint XML file is created as expected.

    Then I change back the PRIMARY KEY to its original VALUE in the input text file (or I take the original file saved before modifying it) and I re-run the package. The package fails again with the same PRIMARY KEY CONSTRAINT VIOLATION ERROR as if the input text file were not changed.

    If I delete the checkpoint file the package runs correctly thus confirming that the input file doesn't have any more duplicated keys in it.

    Did anyone find this behaviour?

    Is there any explaination for it?

  • September 19, 2012 at 6:45 am

    #1539521

    Hi everyone,

    going roundabout in SQLServerCentral.com I've found the article by Bradley Schacht:

    http://www.sqlservercentral.com/blogs/bradleyschacht/2011/04/25/a-caution-about-checkpoints/[/url]

    At the end in this article he states:

    To recap:

    Don’t expect checkpoints to work properly in parallel.

    If a parallel task is RUNNING when another task fails, the package will rerun the non-failing task in addition to the failed task.

    Don’t use checkpoints on containers if you want them to work correctly

    If you put a checkpoint on a sequence container the failed task will be skipped the next time you run the package.

    He seems to assert that we have to avoid the use of checkpoints with every type of container and not only the FOR LOOP and FOREACH LOOP containers. In contrast with Microsoft official docs (it wouldn't be the first time).

    Any further information is welcome.

  • August 20, 2015 at 9:46 am

    #1821455

    I think when the package was failed for the first time due to primary key error it might have already inserted some records into destination table. And when you re ran the package after correcting the file it would have tried to load the all the records from the text file some of which already loaded. In short, Check point doesnt store the info such as what are all records inserted and what are not. Instead you can use transaction property in conjunction with checkpoints.

    So that whenever the task fails it will rollback i.e delete the inserted records from the destination table.

    Thanks,

    Uday

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply