Roadmap › SSIS — SQL Server Integration Services › Module: Error Handling, Logging and Restartability
Lessons in this course (13)
10. Checkpoints and transactions
IntermediateMediumLesson 10 of 13
Checkpoints let a failed package restart from the failed task instead of the beginning:
- Package properties:
CheckpointFileName,CheckpointUsage = IfExists,SaveCheckpoints = True. - Each task to be a restart point:
FailPackageOnFailure = True. - Data flow tasks restart from the beginning of that data flow (not mid-flow) — design them to be idempotent (truncate staging first).
- Not supported well with Foreach Loops (the whole loop reruns) and don't combine checkpoints with transactions casually.
Transactions: TransactionOption property — Required (start or join), Supported (join if exists — default), NotSupported. SSIS transactions use MSDTC (distributed) which must be running and configured. Alternative: native SQL transactions with Execute SQL Tasks (BEGIN TRAN/COMMIT) on a connection manager with RetainSameConnection = True.