SSIS Checkpoint File Object Type issue with complex data types - by mrkb80

Status : 

  Won't Fix<br /><br />
		Due to several factors the product team decided to focus its efforts on other items.<br /><br />
		A more detailed explanation for the resolution of this particular item may have been provided in the comments section.

Sign in
to vote
ID 684346 Comments
Status Closed Workarounds
Type Bug Repros 0
Opened 8/16/2011 8:51:46 AM
Access Restriction Public


When you create an object that is complex (in my case an ArrayList of dictionaries), and store a value into this object, the checkpoint process does not know how to write this value and ends up writing bad xml that causes an invalid checkpoint file to be created.
For example:

Create an ssis variable of type object and use a script task to create a value in the object as such:

ArrayList x = new ArrayList();
Dictionary<string,string> d = new Dictionary<string,string>();


Dts.Variables["VariableObject"].Value = x;

Now turn on checkpoints and intentionally fail the package.  You will notice that the following incomplete xml value is written in the checkpoint file:

<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:Variable>

As you can see once the code that generates the checkpoint hits this variable it tries to write the variable value property and fails, leaving invalid xml which the package cannot use the next time the package is run.  
This leaves the package in state of perpetual failure!
Sign in to post a comment.
Posted by mrkb80 on 8/18/2011 at 11:45 AM
I fully disagree with this logic. The documentation does state that checkpoints do not work with object data types, but that in no way explains the generation of invalid xml in a checkpoint file. If you don't support object data types in checkpoints, then why even write them to the checkpoint file. Even worse, why do SOME object datatypes create invalid xml and not ALL object data types. This is clearly a bug!
Posted by Microsoft on 8/18/2011 at 9:24 AM
Hi mrkb80,
We’re closing this issue as “Won’t Fix.” The Checkpoint feature in SSIS was not built to work with the Object data type. This is called out in the product documentation here:
“…the values of variables that have the Object data type are not saved in checkpoint files.”
Typically packages that use Object variables will populate the variables with a value early in the package execution, often using a Script task or Execute SQL task. Tasks that execute later in the package then consume the object stored in the variable. When this pattern is combined with checkpoints, there is no reliable restart mechanism; the earlier task must be re-run to re-populate the Object variables before later tasks can succeed.