This post is related to this tip: Using checkpoints in SQL Server Integration Services (SSIS) Packages to restart from the point of failure

http://www.mssqltips.com/tip.asp?tip=1408

Hi,

It was a nice overview of SSIS checkpoint funcationality.

We are trying to implement the following scenario.

We have total 1000 records to be inserted into staging area.

We want to implement a logic in SSIS 2005 which will allow us to commit after evrey 50 records inserted. That way if the package fails, then

instead of insterting all the records from the scratch, we will need to insert only records after the point of failure. Means all the records which got inserted before the point of failure are commited and insertion process starts from the record which did not get inserted. Basically we would like to know how to implement logic in the ssis task so that checkpoint after every 50 records are executed.

Regards 

Santosh

The Checkpoint feature allows your package to resume at the point of failure.  So if you had a Data Flow task that failed, when you rerun your package you would begin with the failed Data Flow task, bypassing any steps prior.  However, the Checkpoint feature doesn't record the fact that a portion of a task has completed successfully; it only records the fact that a task has completed successfully.  You can still use checkpoints but you need a little something else as well.

The way I would do this is to implement a batching type of scenario.  Use a FOR LOOP.  The FOR LOOP executes until there are no more rows to process.  Inside of the FOR LOOP you have a Data Flow task and an Execute SQL Task.  The Data Flow task selects the next batch of rows from your source table to process and flags them as in process.  You can call a stored procedure to update a batch of rows as in process then select those rows, for instance using an OLE DB Source in the Data Flow.  Also inside the Data Flow you insert those rows into your destination table using an OLE DB Destination.  Use the Execute SQL task to update the source table rows from inprocess to processed.  Each time the Data Flow task executes within the FOR LOOP, it grabs the next batch of rows to be processed.

 We have a new tip on implementing batch processing in SSIS: http://www.mssqltips.com/tip.asp?tip=1504