SSIS checkpoint file contents, part 2

Understanding checkpoint files

In the last blog on checkpoint files, I was dorking with and investigating their contents, looking to answer the question of whether or not variables were persisted within the process.  You would think they would; after all, if a task craps out, and the next task in line needed those variable values, if they were empty then the restart would most likely crap out as well.  (I’ve never used “crap out” in a sentence twice….a little factoid for you). Anyway, for a project at work I’m thinking of using a recordset variable to store data rather than passing data from table-to-table in an intermediate step, and what I am curious to know is whether or not a recordset from a variable gets pushed to the checkpoint file similarly to how other variable types have their data preserved. Let’s find out.

First I create a simple package with an ExecuteSQLTask and a script task.  I fetch some data from a table in the task, and add a variable of type Object to the result set, and a Result Name to 0.  I then set the ScriptTask ForcedExecutionResult to Failure, and FailPackageOnFailure to True. Then set the CheckpointFileName, the CheckpointUsage to IfExists, and SaveCheckpoints to True. Run the package, and the first task will execute but the second will fail.  Go search for your checkpoint file and open it – here’s a copy of mine:

Figure 1.  Control Flow

Figure 2.  ExecuteSQL Task settings 

Figure 3. Script Task settings so that it fails

Figure 4. Checkpoints properties set

Just like variable values that we saw in the other blog, this Object variable with the recordset values via setting ResultSet equal to Full result set in my ExecuteSQL task sent the entire dataset to the checkpoint file in XML format. 

This is very nice and a cool feature, but it looks like a disaster waiting to happen.  If you if you by chance need to send 100 million rows to you variable you might have a serious problem.  Therefore, consider testing this thoroughly before taking advantage of checkpoint, especially if you are using the Object type.  Check for ample space, and certainly make sure that performance is good.

Figure 5. Persisted recordset in checkpoint file from object type variable

Lee

----------------------

"You go, girl" should probably go! Right along with "You the man!" 
George Carlin