May 10, 2009 at 2:41 pm
I have two tasks in my ssis package and both of them execute sql tasks.
The first one will create a table.
code:if object_id('testcheckpoint') is not null
drop table testcheckpoint
go
create table testcheckpoint(id int)
The second will insert values
insert into testcheckpoint values(1)
insert into testcheckpoint values(2)
insert into testcheckpoint values('e')
insert into testcheckpoint values(4)
The second task will fail after inserting two rows, 'e' is not supported.
I have configured checkpoint, so after i correct that error (replacing 'e' with 3) the execution will start from task 2.
It's again starts from the begining(from the first insert statement), so the values in the table are
1
2
1
2
3
4
is there any way that i can configuret the package so that, i can start inserting from 3 instead of 1 again.
Thanks,
Sam
May 12, 2009 at 12:46 pm
Do you have the "CheckpointUsage" property set? From your example you will need to set "CheckpointUsage" to "IfExists", which causes the package to run from the beginning if the CheckpointFile is not present or to run from the identified point(point of failure) if the file exists.
- Costa
MCTS: BI 2008, MCITP: BI 2008
Stay Thirsty My Friends
May 12, 2009 at 1:07 pm
From the following article
http://www.sqlservercentral.com/articles/Integration+Services/65473/[/url]
1. Checkpoint can be implemented only at the control flow not to the data flow. This means that in case of failure in middle of the data flow, at the re-run of package it data flow will run from the beginning.
2. Checkpoint will not have transaction enable. For example, if there is a task which has several Insert statements and in case of a failure of the one insert statement, other insert statement will not be rollback. In case of a re-run of the package, failed control will execute again and there will be duplicate of data. Therefore it is advisable to introduce transaction to SSIS package
I would also try to put some logic in place while inserting records to only insert the value if its not already present in the database, to make things simpler.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply