zondag 28 november 2010

SSIS : DTEXEC CHECKPOINITNG ON/OFF

When a job with a SSIS package execution is scripted i've noticed that the DTEXEC commandline option is set to /CHECKPOINTING OFF. In my packages i've built checkpoints so this does alarms me a bit. I decided to find out more about this.

In BOL the following fragment of text can be found: "Optional). Sets a value that determines whether the package will use checkpoints during package execution. The value on specifies that a failed package is to be rerun. When the failed package is rerun, the run-time engine uses the checkpoint file to restart the package from the point of failure.
The default value is on if the option is declared without a value. Package execution will fail if the value is set to on and the checkpoint file cannot be found. If this option is not specified, the value set in the package is retained. For more information, see Restarting Failed Packages by Using Checkpoints.
The /CheckPointing on option of dtexec is equivalent to setting the SaveCheckpoints property of the package to True, and the CheckpointUsage property to Always."

This phrase only describes the ON option. The OFF option is not described anyway!


Scenario 1 
In this scenario lets try the option /CHECKPOINTING OFF when the checkpoints are enabled in the SSIS package. In the snippet below you can see that i've set the checkpoint properties:


I opened a DOS window and i executed the following command: DTEXEC /FILE TestWithCheckpointfiles.dtsx /CHECKPOINTING OFF and below you can see the result:


and the checkpoint file is created:


Conclusion: the option CHECKPOINTING OFF doesn't turn off checkpointing (?!), strange and not very intuitive.

Scenario 2
In the next scenario i tried the following setting:

I open a DOS window and i executed the following command: DTEXEC /FILE TestWithCheckpointfiles.dtsx /CHECKPOINTING OFF and below you can see the result:

and no checkpointfiles are created.


Conclusion: as i expected no checkpointfile is created.

Scenario 3
In this scenario let's try the CHECKPOINTING ON option. As in the former scenario the properties for the checkpoints stays the same:


and executing  DTEXEC /FILE TestWithCheckpointfiles.dtsx /CHECKPOINTING ON in the DOS windows shows this:


And the checkpointfile is created:

Conclusion: the option CHECKPOINTING ON overrrides the properties 'CheckpointUsage' and 'SaveCheckpoints' in the SSIS packages.

Scenario 4
In this scenario i left property CheckpointFileName blank. Something like this :


and executing  DTEXEC /FILE TestWithCheckpointfiles.dtsx /CHECKPOINTING ON in the DOS windows shows this:


and offcourse no checkpoint file exists in the folder and that means that it is a goed a idea of specifying the checkpointfile in commandline options of DTEXEC: DTEXEC /FILE CheckpointDTEXEC.dtsx /CHECKPOINTING ON /Checkfile E:\tmp\NowviaCommandline.chkpnt and the properties stays the same. The output of the commandline executions shows this :


 and the checkpoinfile is created:





Conclusion
The option CHECKPOINTING OFF doesn't turn of the checkpointing in the SSIS package, but CHECKPOINTINGON does turn on checkpointing!

Greetz,
Hennie


1 opmerking: