SQL Server Performance Forum – Threads Archive
SSIS transactions and checkpoints
Hello everyone, I had been studying the relationship between SSIS Checkpoints and SSIS Transactions. What I want to do is to create a package with different task, where each one task creates a new transaction, and the same time each task be a checkpoint, it#%92s in order to restarts the package from the failure task not from the beginning. The Transaction-Checkpoint solution contains two packages*:CkeckpointsAndTransactions1.dtsx and CkeckpointsAndTransactions2.dtsx Package CkeckpointsAndTransactions1 contains four tasks, task three always fail. The package is configured to use checkpoints and each individual task creates a checkpoint. Additionally, each task creates a new transaction. The package has the TransactionOption setting to NoSupported. In the CkeckpointsAndTransactions1 package there is something wrong, when the third task fails and I restart the package, the package starts from the beginning, this is wrong!!, the package should restart from the failure task. In order to the package works like is expected it#%92s necessary to add a new task between second and third task. It is also necessary that this new task hasn#%92t transaction support. This is shown in the CkeckpointsAndTransactions2 package, in this package after package failure, I restart the package and the package restarts from the failure task, like is expected, but the additional task should not be necessary!!
Does anyone what is wrong in my packages?? How can I to create a package with different task, where each task creates a new transaction, and the same time each task be a checkpoint? *Please download the BIDS solution from hernan93.files-upload.com (Transaction-Checkpoint.zip file)
Post the relevant warnings and errors you are getting with this looped packages. http://www.databasejournal.com/features/mssql/article.php/3612021 fyi on information about Checkpionts & Transactions. http://blogs.conchango.com/jamietho…1/SSIS-Nugget_3A00_–Ignore-a-checkpoint.aspx a good one on the subject. Satya SKJ
Microsoft SQL Server MVP
Writer, Contributing Editor & Moderator
http://www.SQL-Server-Performance.Com
This posting is provided AS IS with no rights for the sake of knowledge sharing. Knowledge is of two kinds. We know a subject ourselves or we know where we can find information on it.
What I want to do is to create a package with different tasks, where each one task creates a new transaction, and the same time each task be a checkpoint. The articlehttp://www.databasejournal.com/features/mssql/article.php/3612021
reads as follow: "… Note that the result would be different if you left the default value of the package TransactionOption (NotSupported) and set each of the Execute SQL Tasks with Required value, since this would result in three separate transactions (so failure of the second would not trigger rollback of the first)… …relaunching the package will still result in exactly the same sequence of actions, including execution of the first task. To eliminate this unnecessary step, assign a valid path and file name to the CheckpointFileName package property, set Checkpoint usage to IfExists, and SaveCheckpoints to True…
" I did that, and I enabled the package to save checkpoints (with the CheckpointFileName, CheckpointUsage and SaveCheckpoints properties) and set each task to save a checkpoint (with FailPackageOnFailure property). The problem is that it didn’t work as it was supposed to, because when I execute the package, every time it starts form the beggining. The package is not saving checkpoints. What’s happening?? Thank you in advance
Hernán.
You can use the container to make as a transaction….
Check the following blog… SSIS: Package transactions
http://blogs.conchango.com/jamiethomson/archive/2004/12/14/SSIS_3A00_-Package-transactions.aspx
MohammedU.
Moderator
SQL-Server-Performance.com All postings are provided “AS IS†with no warranties for accuracy.
What is the Value for the CheckpointUsage Property you have set? Satya SKJ
Microsoft SQL Server MVP
Writer, Contributing Editor & Moderator
http://www.SQL-Server-Performance.Com
This posting is provided AS IS with no rights for the sake of knowledge sharing. Knowledge is of two kinds. We know a subject ourselves or we know where we can find information on it.
CheckpointUsage = IfExist.
SaveCheckpoint = true. Hernan
You might try using CheckpointUsage= ‘Required’, as CheckpointUsage=’IfExists’ creates the checkpoint file when the package runs. Revising BOL
quote:
The checkpoint file includes the execution results of all completed containers, the current values of system and user-defined variables, and package configuration information. The file also includes the unique identifier of the package. To successfully restart a package, the package identifier in the checkpoint file and the package must match; otherwise the restart fails. This prevents a package from using a checkpoint file written by a different package version. If the package runs successfully, after it is restarted the checkpoint file is deleted.
The checkpoint file includes the execution results of all completed containers, the current values of system and user-defined variables, and package configuration information. The file also includes the unique identifier of the package. To successfully restart a package, the package identifier in the checkpoint file and the package must match; otherwise the restart fails. This prevents a package from using a checkpoint file written by a different package version. If the package runs successfully, after it is restarted the checkpoint file is deleted.
Also I would like to suggest to use the ForceExecutionResult property to test the use of checkpoints in a package. Satya SKJ
Microsoft SQL Server MVP
Writer, Contributing Editor & Moderator
http://www.SQL-Server-Performance.Com
This posting is provided AS IS with no rights for the sake of knowledge sharing. Knowledge is of two kinds. We know a subject ourselves or we know where we can find information on it.
Hello MohammedU. Your link reads as follow: "Note that, by design, a checkpoint will not function if it is placed on a container that enlists in a transaction started from a parent container." This is true, in fact my test works that the article states. But in the test we are discussing the problem is that each task creates his own transaction, them don’t enlist in a parent transaction. Thank you Hernan
quote:Originally posted by satya
You might try using CheckpointUsage= ‘Required’, as CheckpointUsage=’IfExists’ creates the checkpoint file when the package runs.
Revising BOL
Also I would like to suggest to use the ForceExecutionResult property to test the use of checkpoints in a package. Satya SKJ
Microsoft SQL Server MVP
Writer, Contributing Editor & Moderator
http://www.SQL-Server-Performance.Com
This posting is provided AS IS with no rights for the sake of knowledge sharing. Knowledge is of two kinds. We know a subject ourselves or we know where we can find information on it.
Thak you for all your help Satya, I made some test but neither of your answers solve the problem.
Hernán.
quote:
The checkpoint file includes the execution results of all completed containers, the current values of system and user-defined variables, and package configuration information. The file also includes the unique identifier of the package. To successfully restart a package, the package identifier in the checkpoint file and the package must match; otherwise the restart fails. This prevents a package from using a checkpoint file written by a different package version. If the package runs successfully, after it is restarted the checkpoint file is deleted.
The checkpoint file includes the execution results of all completed containers, the current values of system and user-defined variables, and package configuration information. The file also includes the unique identifier of the package. To successfully restart a package, the package identifier in the checkpoint file and the package must match; otherwise the restart fails. This prevents a package from using a checkpoint file written by a different package version. If the package runs successfully, after it is restarted the checkpoint file is deleted.
Also I would like to suggest to use the ForceExecutionResult property to test the use of checkpoints in a package. Satya SKJ
Microsoft SQL Server MVP
Writer, Contributing Editor & Moderator
http://www.SQL-Server-Performance.Com
This posting is provided AS IS with no rights for the sake of knowledge sharing. Knowledge is of two kinds. We know a subject ourselves or we know where we can find information on it.
Then the final option is to raise a call with MS support in this case. Satya SKJ
Microsoft SQL Server MVP
Writer, Contributing Editor & Moderator
http://www.SQL-Server-Performance.Com
This posting is provided AS IS with no rights for the sake of knowledge sharing. Knowledge is of two kinds. We know a subject ourselves or we know where we can find information on it.
]]>