Checkpoints in SSIS: resume a package from the exact point of failure

Every team still running ETL on SQL Server Integration Services knows the scene: a long package — heavy extraction, a few transformations, several loads — fails on the second-to-last task. The obvious option is to rerun the whole package. The problem is that this re-extracts data already in staging, redoes work that already succeeded and, in nightly loads, threatens to blow the processing window.

SSIS Checkpoints exist precisely for this: to write the package's progress to disk and, on re-run, resume from the exact point where it stopped. It's an old, stable and underused feature — and it still pays off in the legacy portfolios that keep critical operations running.

How it works under the hood

When checkpoints are enabled, SSIS writes an XML checkpoint file at the start of execution. As each Control Flow task completes successfully, it records that progress (and the current value of the variables) into the file. If the package fails, the file stays on disk. On the next execution, SSIS reads the file, identifies what was already completed and resumes from the first task that didn't yet finish successfully. When the package runs all the way through without failures, the file is deleted — and recreated on the next execution.

Note the scope: the checkpoint operates at the Control Flow level, not the Data Flow. That is, the restart granularity is the task, not the data row.

The 3 configuration steps

1) Package properties (Control Flow):

• SaveCheckpoints = True — enables writing the checkpoint.
• CheckpointUsage = IfExists — uses the file if it exists; otherwise, runs from the start. (The other options are Never and Always; Always requires the file to exist and fails if it doesn't.)
• CheckpointFileName = <file path> — the location of the checkpoint file.

2) A stable path for the file:

In development, a local path is fine. In production, use a UNC path (\\server\share\package.chk) accessible by the account that runs the package (usually the SQL Server Agent). A local path on the execution server tends to break when the package is moved or run by another node.

3) Property on the critical tasks:

• FailPackageOnFailure = True on each task that should mark a checkpoint point. Without this, the task's failure is not recorded as a resume point.

Pitfalls worth gold

Data Flow is atomic for the checkpoint. If a Data Flow fails midway, the restart happens at the start of that Data Flow — not at the row that failed. So isolate expensive loads into their own tasks: that way a failure further ahead doesn't force you to repeat the heavy extraction before it.

Containers require extra care. Inside a Sequence Container or loops (For Loop, Foreach Loop), set FailParentOnFailure = True on the task and FailPackageOnFailure = True on the container. Important: SSIS does not resume in the middle of a loop — it restarts the current iteration from the beginning. Don't count on a checkpoint to resume inside a Foreach at file 7 of 10; it will go back to the start of the loop.

Transactions and checkpoints are different things. A checkpoint controls what to rerun; it does not undo what was already written. If a task already inserted data and you resume after it, that data is still there. Combine checkpoints with idempotency (loads that can run twice without duplicating): staging truncated per run, MERGE by key, or watermark control.

Changed the package? Discard the old checkpoint. If you edited the Control Flow between the failure and the re-run, the checkpoint file may no longer match the package structure. Delete it and run from scratch.

When to use it (and when not)

Checkpoints shine in packages that are long, sequential and expensive to reprocess — the classic nightly ETL, with a slow extraction followed by several loads. For short, idempotent, cheap-to-rerun packages, the extra complexity rarely pays off: rerunning everything is simpler and more robust.

Worth remembering the 2026 context: Microsoft has practically frozen new SSIS features, and the recommendation for new flows is Fabric Data Factory. But thousands of SSIS packages remain in production, and you can now orchestrate them inside Fabric via the Invoke SSIS Package (Preview) activity, pointing to the same SSISDB. In other words: knowing SSIS well — including resilience with checkpoints — remains money in your pocket while the legacy is modernized at your own pace.

Summary

Checkpoints turn "the package failed, run it all again" into "the package failed, resume where it stopped". Three properties on the package, one on each critical task, a stable file path — and you save load window, reduce reprocessing and gain resilience in pipelines the business can't afford to lose.