SSIS Transaction and checkponts in SQL server 2008 R2

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » Article Discussions » Article Discussions by Author » Discuss Content Posted by Dinesh Asanka » SSIS Transaction and checkponts in SQL server...

SSIS Transaction and checkponts in SQL server 2008 R2

Rate Topic

Display Mode

Topic Options

Author

Message

leporea

Posted Thursday, September 13, 2012 8:24 AM

Forum Newbie

Group: General Forum Members
Last Login: Friday, January 11, 2013 2:16 AM
Points: 2, Visits: 38

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?

Post #1358601

leporea

Posted Wednesday, September 19, 2012 6:45 AM

Forum Newbie

Group: General Forum Members
Last Login: Friday, January 11, 2013 2:16 AM
Points: 2, Visits: 38

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/
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.

Post #1361298

« Prev Topic | Next Topic »

Permissions