Checkpoints don't work

Question

Hi, everybody!

I've made a simple package in order to try the Checkpoints feature of SSIS 2008. The properties of the package are:

CheckpointFileName = e:\1.ttt CheckpointUsage = IfExists SaveCheckpoints = true

It consists of an always fails Execute SQL Task:

FailPackageOnFailure = true SqlStatementSource = select 1/0

The package executing log:

SSIS package "Package.dtsx" starting. Information: 0x40016045 at Package: The package will be saving checkpoints to file "E:\1.ttt" during execution. The package is configured to save checkpoints. Error: 0xC002F210 at Execute SQL Task 1, Execute SQL Task: Executing the query "select 1/0" failed with the following error: "Divide by zero error encountered.". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly. Task failed: Execute SQL Task 1 SSIS package "Package.dtsx" finished: Failure.

Everything seems OK. The trouble is that the created checkpoint file (e:\1.ttt) is empty. What I'm doing wrong?

Answer 1

I have tried the same on SSIS 2005 and its not working. I hope this is a system exception.. so package is failing...

Answer 2

May I know what are the package containers?

Answer 3

There're no containers in the package at all. It consists of only one Execute SQL Task, laying right on the Control Flow tab.

Answer 4

Hi You need to set Execute SQL Task's FailurePackageOnFailure Property to ture.

As i have read following on MSDN documents on CheckpointUsgae.

Additionally, you must set the FailPackageOnFailure property to true for all the containers in the package that you want to identify as restart points

See following link on Check point usage documented on MSDN.
http://msdn.microsoft.com/en-us/library/ms140226.aspx

I did try above settings and works fine.

Best of luck

Answer 5

Hi!

As it's following from my first message, all the properties are set in exactly the way as they are in that MSDN article, notably:

Package.CheckpointFileName = e:\1.ttt
Package.CheckpointUsage = IfExists
Package.SaveCheckpoints = true

Task.FailPackageOfFailure = true

And I've have to repeat that the package consists of only one always failing Task (in truth, there are no containers like Sequence, For, Foreach).

Answer 6

Hi Vasily,

Let me come to your first point that the checkpoint file is empty and this what you are asking for.

Your pacakge contains only one container, Execute SQL Task which is fails because of division error. As per your message you have got only one container and package fails at that stage so package has nothing to save in Checkpoint file and that is why it is empty. Try to put another Execute Task on top of Division By zero execute task. Delete the existing check point file and execute package again, then you will find some information inf the checkpoint file.

Regards,

Balwant.

Answer 7

Hi, Balwant!

You are absolutely right! Just after I added the second 'green' task before the failing one in the sequence, the checkpoint file became non-empty.

Everything is OK now :)