Someone recently asked me if it was possible to dynamically set the location of a checkpoint file. Happily the answer is yes and in actual fact its something that I do all the time so that my packages can all work in conjunction with my Common Folder Structure. Of course, its all achieved with that most venerable of SSIS features, property expressions.
So here's an idea you might like to implement. It builds the location of a checkpoint file dynamically based on the package name. I have 2 variables:
The variable @[User::RootLocation] would typically be set when the package is executed either by a parent package configuration, the /SET option of dtexec.exe or by a configuration.The variable @[User::CheckpointFile] is set dynamically using an expression that includes the name of the package. You can see that expression here:
Once you've got those 2 variables in place setting the location of the checkpoint file is very easy. You simply need to put an expression on the CheckpointFileName property of the package:
again using an expression like so:
That's basically it. All you need to set a checkpoint file name dynamically is property expressions.
-Jamie