How does one decipher a checkpoint file?

Question

Hi,
I'm trying to investigate an issue we're having with checkpoint files - they are not behaving as we suspect and in fact I suspecxt I've found a bug in SSIS. The bug manifests itself as tasks that completed successfully on a previous execution are being run again when the package is restarted from a checkpoint file (and yes, I have configured everything correcrtly regarding the FailPackageOnFailure property).

In order to investigate I need to know how exactly I should read the checkpoint file. For example, take a look at the following:

<DTS:Checkpoint xmlns:DTS="www.microsoft.com/SqlServer/Dts" DTS:PackageID="{9035B34C-A3C7-4231-9F78-2465DB6EFF3B}"><DTS:Variables DTS:ContID="{9035B34C-A3C7-4231-9F78-2465DB6EFF3B}">
<DTS:Variable>
<DTS:Property DTS:Name="Expression"></DTS:Property>
<DTS:Property DTS:Name="EvaluateAsExpression">0</DTS:Property>
<DTS:Property DTS:Name="Namespace">BIPA</DTS:Property>
<DTS:Property DTS:Name="ReadOnly">0</DTS:Property>
<DTS:Property DTS:Name="RaiseChangedEvent">0</DTS:Property>
<DTS:Property DTS:Name="IncludeInDebugDump">2345</DTS:Property><DTS:VariableValue DTS:DataType="8">C:\Projects\UKMDataWarehouse_RootFolder\</DTS:VariableValue>
<DTS:Property DTS:Name="ObjectName">RootFolder</DTS:Property>
<DTS:Property DTS:Name="DTSID">{662ED357-0257-4C98-9146-ADB4A11D7FE2}</DTS:Property>
<DTS:Property DTS:Name="Description"></DTS:Property>
<DTS:Property DTS:Name="CreationName"></DTS:Property></DTS:Variable></DTS:Variables><DTS:Variables DTS:ContID="{3d0ba9db-6259-4d19-99c3-67b3e397289f}"/><DTS:Variables DTS:ContID="{51a0a4f3-5078-4ec1-96fe-a896119b34c9}"/><DTS:Variables DTS:ContID="{86E4CD8B-3F4B-4319-A070-60922504DC9D}"/><DTS:Variables DTS:ContID="{5c1f24a3-382c-441a-9233-2c8c8e37d0e3}">
<DTS:Variable>
<DTS:Property DTS:Name="Expression"></DTS:Property>
<DTS:Property DTS:Name="EvaluateAsExpression">0</DTS:Property>
<DTS:Property DTS:Name="Namespace">User</DTS:Property>
<DTS:Property DTS:Name="ReadOnly">0</DTS:Property>
<DTS:Property DTS:Name="RaiseChangedEvent">0</DTS:Property>
<DTS:Property DTS:Name="IncludeInDebugDump">6789</DTS:Property><DTS:VariableValue DTS:DataType="3">8650</DTS:VariableValue>
<DTS:Property DTS:Name="ObjectName">TaskWorkHistoryID</DTS:Property>
<DTS:Property DTS:Name="DTSID">{9da48f31-de89-4932-af41-77ef256e5837}</DTS:Property>
<DTS:Property DTS:Name="Description"></DTS:Property>
<DTS:Property DTS:Name="CreationName"></DTS:Property></DTS:Variable>
<DTS:Variable>
<DTS:Property DTS:Name="Expression"></DTS:Property>
<DTS:Property DTS:Name="EvaluateAsExpression">0</DTS:Property>
<DTS:Property DTS:Name="Namespace">User</DTS:Property>
<DTS:Property DTS:Name="ReadOnly">0</DTS:Property>
<DTS:Property DTS:Name="RaiseChangedEvent">0</DTS:Property>
<DTS:Property DTS:Name="IncludeInDebugDump">6789</DTS:Property><DTS:VariableValue DTS:DataType="3">0</DTS:VariableValue>
<DTS:Property DTS:Name="ObjectName">Variable</DTS:Property>
<DTS:Property DTS:Name="DTSID">{A944CAF1-3530-4548-BC46-5F4D6432CADF}</DTS:Property>
<DTS:Property DTS:Name="Description"></DTS:Property>
<DTS:Property DTS:Name="CreationName"></DTS:Property></DTS:Variable></DTS:Variables><DTS:Variables DTS:ContID="{92b49aaf-e411-465a-b15d-059fc1fac0a1}"/><DTS:Variables DTS:ContID="{cd7a2502-d23d-4f34-848d-41543b1dcbfb}"/><DTS:Variables DTS:ContID="{e86409b8-c83b-4a38-bc97-994f264835e2}"/><DTS:Variables DTS:ContID="{b848e772-7c53-459e-a4e6-34e68d6931fd}"/><DTS:Variables DTS:ContID="{b8559e4e-006f-4aff-9cac-ebdfa14f9db6}"/><DTS:Variables DTS:ContID="{701de1be-1f9d-4c77-b597-6ef305ab25a4}">
<DTS:Variable>
<DTS:Property DTS:Name="Expression"></DTS:Property>
<DTS:Property DTS:Name="EvaluateAsExpression">0</DTS:Property>
<DTS:Property DTS:Name="Namespace">User</DTS:Property>
<DTS:Property DTS:Name="ReadOnly">0</DTS:Property>
<DTS:Property DTS:Name="RaiseChangedEvent">0</DTS:Property>
<DTS:Property DTS:Name="IncludeInDebugDump">6789</DTS:Property><DTS:VariableValue DTS:DataType="3">8650</DTS:VariableValue>
<DTS:Property DTS:Name="ObjectName">TaskWorkHistoryID</DTS:Property>
<DTS:Property DTS:Name="DTSID">{9da48f31-de89-4932-af41-77ef256e5837}</DTS:Property>
<DTS:Property DTS:Name="Description"></DTS:Property>
<DTS:Property DTS:Name="CreationName"></DTS:Property></DTS:Variable>
<DTS:Variable>
<DTS:Property DTS:Name="Expression"></DTS:Property>
<DTS:Property DTS:Name="EvaluateAsExpression">0</DTS:Property>
<DTS:Property DTS:Name="Namespace">User</DTS:Property>
<DTS:Property DTS:Name="ReadOnly">0</DTS:Property>
<DTS:Property DTS:Name="RaiseChangedEvent">0</DTS:Property>
<DTS:Property DTS:Name="IncludeInDebugDump">6789</DTS:Property><DTS:VariableValue DTS:DataType="3">0</DTS:VariableValue>
<DTS:Property DTS:Name="ObjectName">Variable</DTS:Property>
<DTS:Property DTS:Name="DTSID">{A944CAF1-3530-4548-BC46-5F4D6432CADF}</DTS:Property>
<DTS:Property DTS:Name="Description"></DTS:Property>
<DTS:Property DTS:Name="CreationName"></DTS:Property></DTS:Variable></DTS:Variables><DTS:Variables DTS:ContID="{111cc132-db36-4dc4-a0b8-b078f64d9ba2}"/><DTS:Container DTS:ContID="{15A4DF2B-550E-4D28-8380-B4222CB09D87}" DTS:Result="0" DTS:PrecedenceMap=""/><DTS:Container DTS:ContID="{52F7F52D-6CA9-4ECC-BDB2-4498B07B791D}" DTS:Result="0" DTS:PrecedenceMap=""/><DTS:Container DTS:ContID="{7E54CF37-B109-4C2A-9044-2C5F45F3BB5C}" DTS:Result="0" DTS:PrecedenceMap="Y"/><DTS:Container DTS:ContID="{8D398338-A1BA-4486-9207-7A0F1998F077}" DTS:Result="0" DTS:PrecedenceMap=""/><DTS:Container DTS:ContID="{1424DA3B-6EE2-4D58-997D-BE1073712F2D}" DTS:Result="0" DTS:PrecedenceMap="Y"/><DTS:Container DTS:ContID="{5F2F4045-2C96-41BA-88C1-E8F91E6865D0}" DTS:Result="0" DTS:PrecedenceMap=""/><DTS:Container DTS:ContID="{26ADEA45-9474-41F9-BB4F-5775F8FC2137}" DTS:Result="0" DTS:PrecedenceMap=""/><DTS:Container DTS:ContID="{A6AF7946-1587-476D-815C-339333B89B26}" DTS:Result="0" DTS:PrecedenceMap="Y"/><DTS:Container DTS:ContID="{7F93DA53-AB7A-4A07-AA3C-162195FF0F74}" DTS:Result="0" DTS:PrecedenceMap=""/><DTS:Container DTS:ContID="{D0502E5B-5C42-4BEB-966B-9F722A584836}" DTS:Result="0" DTS:PrecedenceMap=""/><DTS:Container DTS:ContID="{B210025D-782A-4B03-B93F-ED828F81FAE5}" DTS:Result="0" DTS:PrecedenceMap=""/><DTS:Container DTS:ContID="{26191E5D-3977-4C33-9903-CE411424B482}" DTS:Result="0" DTS:PrecedenceMap=""/><DTS:Container DTS:ContID="{E9010662-1116-4C9D-A14F-0EB50112BC3F}" DTS:Result="0" DTS:PrecedenceMap=""/><DTS:Container DTS:ContID="{AE191362-83F3-4035-8B18-F0121F667CA6}" DTS:Result="0" DTS:PrecedenceMap=""/><DTS:Container DTS:ContID="{9C29236B-B940-4167-952F-FC889317A34A}" DTS:Result="0" DTS:PrecedenceMap="Y"/><DTS:Container DTS:ContID="{22DEC385-61FF-444C-9DEF-19ADDBEC3035}" DTS:Result="0" DTS:PrecedenceMap=""/><DTS:Container DTS:ContID="{CFAAA18B-F1D9-4EE2-8D3D-1B31667BD2B5}" DTS:Result="0" DTS:PrecedenceMap=""/><DTS:Container DTS:ContID="{2E74AE8C-AA08-4465-9886-06B055EC4904}" DTS:Result="0" DTS:PrecedenceMap="Y"/><DTS:Container DTS:ContID="{CB6CC28F-BC9A-40F1-953A-3F7134B9BB5B}" DTS:Result="0" DTS:PrecedenceMap=""/><DTS:Container DTS:ContID="{8CF7F6A2-9200-4833-B1FB-8709E15ADCB8}" DTS:Result="0" DTS:PrecedenceMap=""/><DTS:Container DTS:ContID="{1001B2A6-9DDC-410B-93FA-2ACFCE9A217F}" DTS:Result="0" DTS:PrecedenceMap=""/><DTS:Container DTS:ContID="{E45007A9-009F-4515-BE38-92464DFA4D3B}" DTS:Result="0" DTS:PrecedenceMap="Y"/><DTS:Container DTS:ContID="{161A58B4-D842-4128-81C7-A1BA378FC3A5}" DTS:Result="0" DTS:PrecedenceMap="Y"/><DTS:Container DTS:ContID="{EE2195B9-F476-4A51-B30D-5F1AD11EDAE4}" DTS:Result="0" DTS:PrecedenceMap=""/><DTS:Container DTS:ContID="{F4D418C2-DF10-4FC8-B8A9-318C24A8D219}" DTS:Result="0" DTS:PrecedenceMap=""/><DTS:Container DTS:ContID="{3FA370C2-BC98-4946-8BED-7C2D259F2D12}" DTS:Result="0" DTS:PrecedenceMap=""/><DTS:Container DTS:ContID="{DF026BEB-4609-4527-AF25-A943D8E17C71}" DTS:Result="0" DTS:PrecedenceMap=""/><DTS:Container DTS:ContID="{DAF77CF1-491C-4A6E-A8FB-0D63CF54D88C}" DTS:Result="0" DTS:PrecedenceMap=""/><DTS:Container DTS:ContID="{C2744FF8-8495-4222-BDBF-B758BCD24CDF}" DTS:Result="0" DTS:PrecedenceMap=""/></DTS:Checkpoint>

Something to notice:

1.  Some of the <DTS:Container> elements have DTS:PrecedenceMap="Y" whereas others have DTS:PrecedenceMap="".

What do these values mean?
Also, are there any circumstances that may cause these elements to get written incorrectly (I'm particularly interested in any problems when running tasks in parallel)?

Thanks
Jamie

N.B. Escalated to SSIS team!

---

http://sqlblog.com/blogs/jamie_thomson/ | http://jamiethomson.spaces.live.com/ | @jamiet

Answer 1

Jamie,

It looks as thought the Precedence Map of "" means that there are no other objects with precedence constraints based on that task.  In other words, there is nothing connected to it.  It also looks like the precedence of "Y" means that something else WAS dependent on it.  Of course, this is just me looking around at a sample package I wrote to test checkpoints.

EDIT: Of course, there can still be other tasks with precedence based on these tasks if you have them wrapped in other containers.  But it will appear in the checkpoint as though they do not have these relations.

And I'm not sure if this is similar or not, but in the past I have found that anything run in parallel is not written to the checkpoint after the first failure.  In other words, if we have a container running tasks A, B, C and D - If task A finishes, then Task B fails, then Tasks C and D finish when the package is rerun it will rerun tasks B, C and D (but not A).

---

Please mark answered posts. Thanks for your time.

Answer 2

Thanks for the reply Eric.

Jamie,

It looks as thought the Precedence Map of "" means that there are no other objects with precedence constraints based on that task.  In other words, there is nothing connected to it.  It also looks like the precedence of "Y" means that something else WAS dependent on it.  Of course, this is just me looking around at a sample package I wrote to test checkpoints.

EDIT: Of course, there can still be other tasks with precedence based on these tasks if you have them wrapped in other containers.  But it will appear in the checkpoint as though they do not have these relations.

Hmm, not sure. I thought that that was the signifier that soemthing has completed successfully. I'll await confirmation from the SSIS team.

And I'm not sure if this is similar or not, but in the past I have found that anything run in parallel is not written to the checkpoint after the first failure.  In other words, if we have a container running tasks A, B, C and D - If task A finishes, then Task B fails, then Tasks C and D finish when the package is rerun it will rerun tasks B, C and D (but not A).

---

Please mark answered posts. Thanks for your time.

Yes, that concurs EXACTLY with what I'm seeing. Which, in my opinion, is a big fat hairy bug. Again, awaiting confirmation.

Regards
Jamie

---

http://sqlblog.com/blogs/jamie_thomson/ | http://jamiethomson.spaces.live.com/ | @jamiet

Answer 3

I'm very indecisive in regards to whether or not this is a bug.  On the one hand, you want the state of the package to be exactly what it was when the package failed as a checkpoint is more than just a piece of XML to tell where to start from again.  It also helps us see the variable states, what has completed prior to the failures, etc.  So, if the development team decided that no updates to the checkpoint should be recorded after the failure I understand the reasons.  On the other hand, you now have a number of tasks that have completed that you will be rerunning?  That seems off to me as well.

What I have ended up having to do in our large data loads is setting a task to see if this (child) package has been executed for the particular parent's run successfully already using our audit trails.  If so, I just exit without doing anything else.  This seems very kludgy to me, but it is what I found to work...

---

Please mark answered posts. Thanks for your time.

Answer 4

This is why I don't use checkpoints....

---

John Welch | www.mariner-usa.com | www.agilebi.com | ssisUnit.codeplex.com

Answer 5

DTS:PrecesdenceMap contains zero or more 'Y' or 'N' (in any combination, one for each precedence constraint) to indicate if the outgoing constraint(s) is met or not (evaluate to TRUE). This is strictly internal representation and can change in near future.
I don't think this value is actually used by SSIS engine today (I could be wrong) so I wouldn't read too much into this