Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


SSIS Transaction and checkponts in SQL server 2008 R2


SSIS Transaction and checkponts in SQL server 2008 R2

Author
Message
leporea
leporea
Forum Newbie
Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)

Group: General Forum Members
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?
leporea
leporea
Forum Newbie
Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)

Group: General Forum Members
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.
kukr007
kukr007
Forum Newbie
Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)

Group: General Forum Members
Points: 4 Visits: 16
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
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search