Check Points Enabling in Loop Iterations

Question

Hiii

I have created package of using for each loop container inside contains so many tasks and i set container and each task fail package on failure 'TRUE'.. but when error RAISED i cant able to restart from where error raised so can u pls help me..

Answer 1

Hi,

Enable check points, specify the file name where you wish to create it.. just check when package got failed, file is created or not..

chane the checkpoint usage to always if you wish to have checkpoint always(If you choose this option put a dummy file there before executing.) else go for ifexists property..

Answer 2

Hiii,

I set this properties like what you have shown in screen shot, but its not possible..

Answer 3

You should first confirm your checkpoint files are being created. If your package is failing, checkpoint file should exist.

What kind of task you have used in the For Loop container. Checkpoints do not work if you are dealign with Object Data Type at the task level.

SSIS: Checkpoints implementation

• SSIS: What is CheckPoints?

---

Vikash Kumar Singh || www.singhvikash.in

Answer 4

I have a package with following task

1). ForEach Loop container for reading multiple files

2). Inside container, I have Execute SQL Task for Truncating staging table

3). Next Data Flow Task with Flat file source and OLE DB destination

4). Next Execute SQL Task to execute stored procedure

5). Last, File System task to move file to success folder

 I have configure checkpoint as follows

Package Properties:

CheckPointFileName: D:\CheckPointFile.xml

CheckPointUsage: IfExists

SaveCheckPoints: True

For EachLoop Container Properties: FailPackageOnFailure= TRUE

Answer 5

Hi Supreeth TR,

In a Foreach Loop, the control flow is repeated for every member of an enumerator, or collection. However, neither the information about the state of the enumerator, nor about the number of iterations the loop has gone through, is saved to the checkpoint file. This means that Foreach Loops will always iterate through all items in the enumerator, even when checkpoints are enabled. Please refer to the article about using Containers with Checkpoints: http://www.sqlservercentral.com/articles/Integration+Services+(SSIS)/65809/  

Here are some of the key points from this article.
1. Variables of type Object are never saved to the checkpoint file.
2. Although variable values are saved in the checkpoint file, those values are not used in the expressions of a For Loop or Foreach Loop.
3. A For Loop and a Foreach Loop will always be re-evaluated, even when checkpoints are enabled.

Please feel free to ask if you have any question.

Thanks,
Eileen