Checkpoints in SQL Server Integration Services across multiple Packages

SQL Server 2005 Integration Services (SSIS) can restart failed packages from the point of failure, instead of rerunning the whole package and it depends upon Check Point File.

Check Point does not contain statements (SQL statements) that generated the data but it maintains history of what tasks need to restart. Sample Checkpoint output file (it should be .xml)

<DTS:Checkpoint xmlns:DTS="www.microsoft.com/SqlServer/Dts" DTS:PackageID="{B4EB440E-2CD8-412C-A79B-7461E0F9065F}"><DTS:Variables DTS:ContID="{B4EB440E-2CD8-412C-A79B-7461E0F9065F}"/><DTS:Container DTS:ContID="{8CE70AD0-684D-41D0-AD8E-511850925630}" DTS:Result="0" DTS:PrecedenceMap=""/></DTS:Checkpoint>

These Guid’s are just tasks that need be restarted and nothing else. To configure Check Point

* CheckpointFileName=Specify File Name

* CheckpointUsage=IfExists

* SaveCheckpoints=True

When dealing with Parent and Child Package Checkpoints should be configured else execution results will vary.

In this example I have created a Master package or Parent Package that Creates SQL table using Execute SQL Task and then invokes 2 different Child Packages which insert data into table created.

 

Package (Parent):

In Execute SQL Task created table as below:

IF EXISTS (SELECT        *
                                  FROM            sys.objects

                                  WHERE        name LIKE 'Multi_Level_CheckPoint_Test')
BEGIN
drop table Multi_Level_CheckPoint_Test
CREATE TABLE Multi_Level_CheckPoint_Test(i int PRIMARY KEY)
END

 

Child 1 has again a Execute SQL Task that inserts rows into SQL Table created in parent Package:

 

Insert into Multi_Level_CheckPoint_Test values(1)

Child 2 Package is similar to Child 1 in operation but it has 2 Execute SQL tasks with 2 insert statements.

 

* Insert into Multi_Level_CheckPoint_Test values(2)

* Insert into Multi_Level_CheckPoint_Test values(1) --- This would fail as table column is primary key and we are inserting duplicate row (1 is already inserted in Child1. Execute SQL Task)

After creating packages:

* Parent  - CheckPoint Enabled

* Child  - Checkpoint not enabled

First execution it would fail at second Insert of Child 2 which is understandable as we are trying to insert duplicate rows. But subsequent executions, It would fail again at Child2 but at first insert statement..

This is because checkpoint file at Parent indicates that Child2 needs to be executed again but since Child2 does not have Check Point enabled it starts from First Execute SQL Task which would fail again.

So, in multilevel package executions checkpoints should enabled at each package level and not just at parent level. It would have been nice if Child inherits Parents Checkpoint but there again the question would be do all task have to checkpoint enabled or only some and if only few which of few. Thus onus lies on developer to properly maintain checkpoints.