SSIS checkpoint file contents

A peek inside a checkpoint file…good stuff.

Someone asked me the other day whether user variables were somehow preserved when using SSIS checkpoints in SQL Server.  I honestly couldn’t answer, because I really haven’t used checkpoints that often.  Many of the SSIS packages that I have built were easily rerun; if I had SSIS when I worked back at Transamerica Life/Aegon as the Lead DBA for Data Warehousing back from 2002-2004 I might have used them, because we loaded the data warehouse monthly and it took about 36 hours for the thing to complete.  Other than that, my stuff, either in warehousing or OLTP nightly processing, has taken between 20 minutes and an hour or so.

Anyway, the answer is yes, the information is persisted in the checkpoint file. Sweet!  Here’s the contents of one that I tested a few minutes ago:

<DTS:Checkpoint xmlns:DTS="www.microsoft.com/SqlServer/Dts" DTS:PackageID="{17CC0375-BC2D-4096-AE97-E0F0C498A053}">
<DTS:Variables DTS:ContID="{17CC0375-BC2D-4096-AE97-E0F0C498A053}">

<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">2345</DTS:Property><DTS:VariableValue DTS:DataType="8">a</DTS:VariableValue>

<DTS:Property DTS:Name="ObjectName">Var</DTS:Property>

<DTS:Property DTS:Name="DTSID">{37798F1A-0F2E-4666-8DD3-AD933729DD35}</DTS:Property>

<DTS:Property DTS:Name="Description"></DTS:Property>

<DTS:Property DTS:Name="CreationName"></DTS:Property></DTS:Variable></DTS:Variables>
<DTS:Container DTS:ContID="{531455DF-BDBD-4423-9C95-6800310B6FAD}" DTS:Result="0" DTS:PrecedenceMap=""/></DTS:Checkpoint>

You can see that the DTS:VariableValue includes the data type and the value – a – for my variable. Very nice!  So when the problem is fixed, and the package rerun, it starts at the particular task that it failed on, and picks up the variable values from the file. Remember that the values are normally set at runtime, so this is super handy for it to pick up where it left off.

Happy Checkpointing!

Lee

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

You thought I was going to dis’ SSIS some more, didn’t ya?