how to use Transactionoption and checkpoint features together in SSIS in the scenario as explained in my post?

user_10251155 0
Viewed 70

I have been trying to understand the checkpoint feature in SSIS. For this implementation, I have following configuration inclusion:

  1. I took 4 execute-sql-tasks in a sequence connected with precedence constraints:Success; the 3rd execute-sql-task would fail in the first attempt and thereby I shall fix the error and re-run the package to observe the Checkpoint feature behavior

  2. for the package, the transactionoption:Supported, and for each of the execute-sql-task the transactionoption:Supported

  3. set the checkpoint configuration on package properties as follows:

  • CheckPointFileName: path for the file
  • CheckPointUsage: ifExists
  • SaveCheckPoints: true
  1. for each of the execute-sql-task set the property for:
  • FailPackageonFailure:True

With this configuration, for the first run of package, the first 2 tasks executed with updates on the table in sqlserver, and 3rd task failed, and 4th task didn't start at all since it has precedence-constraint:success with its precedence-constraint task; checkpoint file was created, I fixed the error and re-ran the the package. This time, only task 3 was executed along with task 4 with updates on database; this clearly shows the intended behavior of Checkpoint.

However, then I made a change with the TransactionOption:Required for each of the 4 tasks, and package TransactionOption:Supported; I executed the package twice to observe the Checkpoint behavior. Here, I made an observation --- though the task 1 and task 2 were successful in the first run, they were executed again in the second-run along with task 3 and task 4 with updates on my table. I believe, with Checkpoint configuration, the package have to start from failed task (and execute the following unexecuted tasks); which didn't happen as expected.

Well, my concern in brief is, what is the difference between the two scenarios, say, Scenario 01 ----
A) Package TransactionOption:supported -- B) each of Execute-sql-tasks Transaction:supported -- C) checkpoint: configured

Scenario 02 --- A) Package TransactionOption:supported -- B) each of Execute-sql-tasks Transaction:required -- C) checkpoint: configured.

Would anyone please help me understand this scenario? Thank you for your giving your valuable support.

ssis
1 Answers

Reading Microsoft Documentation on Checkpoints, there is the following note.

Using checkpoints and transactions in the same package could cause unexpected results. For example, when a package fails and restarts from a checkpoint, the package might repeat a transaction that has already been successfully committed.

There are other limitations of checkpoint feature like not supporting For Each Loops. Data Flows will be rerun completely, checkpoints do not capture Data Flow intermediate state.

Checkpoint is a good tool with its limits; it is not a silver bullet for package statefull restart.

user_10106220
Related