I have a package that uses checkpoint restart. It is resposible for truncatings many sets of tables and then loading them. There are several ExecuteSQL tasks to truncate the tables and several corresponding data flows to accomplish the loads.
If a load fails I want the corresponding truncate task to be part of the restart otherwise duplicate data may be loaded. Normally, SSIS will start at the failed task. I read something about containers that led me to think that if I put the truncate & matching load pair in a sequence container that the container would be the restart point, but either I read it wrong or it's not working that way.
Anybody know how to accomplish what I want to do
Checkpoint restart
lukin
Follow your instructions (sequence container FailPackageOnFailure = True and contained tasks FailParentOnFailure = true). After a failure in the load task, the package restart does indeed occur from the beginning of the sequence container, but none of the contained tasks run at all.
First, is this what you're seeing
Second, must the sequence container (or package) have its TransactionOption set to 'Required' as well, for the container to be the restart point
Fairfield
Jaegd,
Looks like you have your tasks checkpointed in the scenario you mentioned. Is that correct
For this to work, your task should not be checkpointed.
i.e. on the tasks, set 'FailPackageOnFailure' to 'False'.
This approach does work for me regardless of whether the container is transacted or not.
In the meantime, I'll see where I can post an example package.
Jtbingham
The container approach does work but you'll have to set the options correctly.
Try this:
What you are doing here is checkpointing the container but not the tasks and when you have a failure you are failing the container thus creating a checkpoint at the container level.
A restart will always start from the beginning of the container.
Ralf Welt
This is an old thread and upon re-reading it today I realised I had submitted something on Connect about it and got a relly good response from Craig Guyer:
https://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx FeedbackID=126489
If you think this would be a good feature to have, click through, comment and vote.
Use the 'ForEach Item' enumerator and just put one item in the collection.
-Jamie
DJS330
Gordon,
That's an interesting question - I'd like to think that it is possible - but I don't know.
There is a workaround - its a bit messy but it will work. A ForEach Loop is the unit of restartability, rather than the containers inside it so if you set up a ForEach loop that only looped once with a matching Execute SQL Task and data-flow inside it - they would both execute on restart.
-Jamie
NASSONS
thelonesoldier
Thanks Jamie,
I'll give that a try after I try something else first. I'm gonna try and get the data flow to completely rollback by putting it in a transaction using the Required setting. That way I won't have to rerun the truncate step. My first attemp raised a DTC error just like the ones you have documented in the past. These are corporate servers so I don't know if firewalls are at play between the servers in question. Anyway, I've read your comments on that and have forwarded them to our tech guys.
Gordy
sheepshower
I added my vote.
Tried the ForEachLoop with one item and it works like a champ. Thanks, you saved my bacon!
programmer newb
Did you ever solve this problem I have exactly the same issue, need to re-run a truncate when a dependent task fails. I tried adding an error handler for OnTaskFailure for the dependent task that ran the truncate and that works - the first time the package is run. When it is restarted all is well, but a second restart fails because the task executed by the error handler is marked as successful executed!
Regarding Jamie's approach: I cannot figure out how to make the ForEachLoop container "loop just once". All the samples seem to be oriented around files & record sets.
RajaGanapathy
In the meantime, here's a blog entry which contains the code for C# console app that will generate a package (thanks to Ivolva). The package contains a single sequence container, with two contained tasks, the second of which will fail half the time, and configured with the sequence container set the FailPackageOnFailure=true, and the tasks only non-default setting to FailParentOnFailure=true.
http://jaegd.spaces.live.com/blog/cns!A9CD33B14B4B8353!117.entry
Here's the xml for the package as well, since we can't add attachments on this forum.
< xml version="1.0" ><DTS:Executable xmlns:DTS="www.microsoft.com/SqlServer/Dts" DTS:ExecutableType="MSDTS.Package.1"><DTS:Property DTS:Name="PackageFormatVersion">2</DTS:Property><DTS:Property DTS:Name="VersionComments"></DTS:Property><DTS:Property DTS:Name="CreatorName">FPS\jaegd</DTS:Property><DTS:Property DTS:Name="CreatorComputerName">FPS</DTS:Property><DTS:Property DTS:Name="CreationDate" DTS:DataType="7">12/9/2006 12:53:33 PM</DTS:Property><DTS:Property DTS:Name="PackageType">5</DTS:Property><DTS:Property DTS:Name="ProtectionLevel">1</DTS:Property><DTS:Property DTS:Name="MaxConcurrentExecutables">-1</DTS:Property><DTS:Property DTS:Name="PackagePriorityClass">0</DTS:Property><DTS:Property DTS:Name="VersionMajor">1</DTS:Property><DTS:Property DTS:Name="VersionMinor">0</DTS:Property><DTS:Property DTS:Name="VersionBuild">8</DTS:Property><DTS:Property DTS:Name="VersionGUID">{BE15E67D-BFF8-4ED0-BA4E-FFAF36D3B304}</DTS:Property><DTS:Property DTS:Name="EnableConfig">0</DTS:Property><DTS:Property DTS:Name="CheckpointFileName">AtomicSequence.xml</DTS:Property><DTS:Property DTS:Name="SaveCheckpoints">-1</DTS:Property><DTS:Property DTS:Name="CheckpointUsage">1</DTS:Property><DTS:Property DTS:Name="SuppressConfigurationWarnings">0</DTS:Property><DTS:Property DTS:Name="ForceExecValue">0</DTS:Property><DTS:Property DTS:Name="ExecValue" DTS:DataType="3">0</DTS:Property><DTS:Property DTS:Name="ForceExecutionResult">-1</DTS:Property><DTS:Property DTS:Name="Disabled">0</DTS:Property><DTS:Property DTS:Name="FailPackageOnFailure">0</DTS:Property><DTS:Property DTS:Name="FailParentOnFailure">0</DTS:Property><DTS:Property DTS:Name="MaxErrorCount">1</DTS:Property><DTS:Property DTS:Name="ISOLevel">1048576</DTS:Property><DTS:Property DTS:Name="LocaleID">1033</DTS:Property><DTS:Property DTS:Name="TransactionOption">1</DTS:Property><DTS:Property DTS:Name="DelayValidation">0</DTS:Property>
<DTS:LoggingOptions><DTS:Property DTS:Name="LoggingMode">0</DTS:Property><DTS:Property DTS:Name="FilterKind">1</DTS:Property><DTS:Property DTS:Name="EventFilter" DTS:DataType="8"></DTS:Property></DTS:LoggingOptions>
<DTS:Executable DTS:ExecutableType="STOCK:SEQUENCE"><DTS:Property DTS:Name="ForceExecValue">0</DTS:Property><DTS:Property DTS:Name="ExecValue" DTS:DataType="3">0</DTS:Property><DTS:Property DTS:Name="ForceExecutionResult">-1</DTS:Property><DTS:Property DTS:Name="Disabled">0</DTS:Property><DTS:Property DTS:Name="FailPackageOnFailure">1</DTS:Property><DTS:Property DTS:Name="FailParentOnFailure">0</DTS:Property><DTS:Property DTS:Name="MaxErrorCount">1</DTS:Property><DTS:Property DTS:Name="ISOLevel">1048576</DTS:Property><DTS:Property DTS:Name="LocaleID">-1</DTS:Property><DTS:Property DTS:Name="TransactionOption">1</DTS:Property><DTS:Property DTS:Name="DelayValidation">0</DTS:Property>
<DTS:LoggingOptions><DTS:Property DTS:Name="LoggingMode">0</DTS:Property><DTS:Property DTS:Name="FilterKind">1</DTS:Property><DTS:Property DTS:Name="EventFilter" DTS:DataType="8"></DTS:Property></DTS:LoggingOptions>
<DTS:Executable DTS:ExecutableType="Microsoft.SqlServer.Dts.Tasks.ScriptTask.ScriptTask, Microsoft.SqlServer.ScriptTask, Version=9.0.242.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91" DTS:ThreadHint="0"><DTS:Property DTS:Name="ExecutionLocation">0</DTS:Property><DTS:Property DTS:Name="ExecutionAddress"></DTS:Property><DTS:Property DTS:Name="TaskContact"></DTS:Property><DTS:Property DTS:Name="ForceExecValue">0</DTS:Property><DTS:Property DTS:Name="ExecValue" DTS:DataType="3">0</DTS:Property><DTS:Property DTS:Name="ForceExecutionResult">-1</DTS:Property><DTS:Property DTS:Name="Disabled">0</DTS:Property><DTS:Property DTS:Name="FailPackageOnFailure">0</DTS:Property><DTS:Property DTS:Name="FailParentOnFailure">1</DTS:Property><DTS:Property DTS:Name="MaxErrorCount">1</DTS:Property><DTS:Property DTS:Name="ISOLevel">1048576</DTS:Property><DTS:Property DTS:Name="LocaleID">-1</DTS:Property><DTS:Property DTS:Name="TransactionOption">1</DTS:Property><DTS:Property DTS:Name="DelayValidation">0</DTS:Property>
<DTS:LoggingOptions><DTS:Property DTS:Name="LoggingMode">0</DTS:Property><DTS:Property DTS:Name="FilterKind">1</DTS:Property><DTS:Property DTS:Name="EventFilter" DTS:DataType="8"></DTS:Property></DTS:LoggingOptions><DTS:Property DTS:Name="ObjectName">SCR Simulate Truncate</DTS:Property><DTS:Property DTS:Name="DTSID">{F4FE90C9-C8DE-4B40-8A6D-63EAE102B563}</DTS:Property><DTS:Property DTS:Name="Description">Script Task</DTS:Property><DTS:Property DTS:Name="CreationName">Microsoft.SqlServer.Dts.Tasks.ScriptTask.ScriptTask, Microsoft.SqlServer.ScriptTask, Version=9.0.242.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91</DTS:Property><DTS:Property DTS:Name="DisableEventHandlers">0</DTS:Property><DTS:ObjectData><ScriptProject Name="ScriptTask_8bd63ea4fc1f416f8d4f57f696fab4f0" Language="Microsoft Visual Basic .NET" EntryPoint="ScriptMain" SaveBinaries="False" ReadOnlyVariables="" ReadWriteVariables=""><ProjectItem Name="dts://Scripts/ScriptTask_8bd63ea4fc1f416f8d4f57f696fab4f0/ScriptMain.vsaitem">
<![CDATA[' Microsoft SQL Server Integration Services Script Task
' Write scripts using Microsoft Visual Basic
' The ScriptMain class is the entry point of the Script Task.
Imports System
Imports System.Data
Imports System.Math
Imports Microsoft.SqlServer.Dts.Runtime
Public Class ScriptMain
' The execution engine calls this method when the task executes.
' To access the object model, use the Dts object. Connections, variables, events,
' and logging features are available as static members of the Dts class.
' Before returning from this method, set the value of Dts.TaskResult to indicate success or failure.
'
' To open Code and Text Editor Help, press F1.
' To open Object Browser, press Ctrl+Alt+J.
Public Sub Main()
'
' Add your code here
'
Dts.TaskResult = Dts.Results.Success
End Sub
End Class]]></ProjectItem><ProjectItem Name="dts://Scripts/ScriptTask_8bd63ea4fc1f416f8d4f57f696fab4f0/ScriptTask_8bd63ea4fc1f416f8d4f57f696fab4f0.vsaproj">
<![CDATA[<VisualStudioProject>
<VisualBasic
Version = "8.0.50727.42"
MVID = "{00000000-0000-0000-0000-000000000000}"
ProjectType = "Local"
ProductVersion = "8.0.50727"
SchemaVersion = "2.0"
>
<Build>
<Settings
DefaultNamespace = "ScriptTask_8bd63ea4fc1f416f8d4f57f696fab4f0"
OptionCompare = "0"
OptionExplicit = "1"
OptionStrict = "1"
ProjectName = "ScriptTask_8bd63ea4fc1f416f8d4f57f696fab4f0"
ReferencePath = "C:\WINDOWS\assembly\GAC_MSIL\Microsoft.SqlServer.ManagedDTS\9.0.242.0__89845dcd8080cc91\;C:\WINDOWS\assembly\GAC_MSIL\Microsoft.SqlServer.ScriptTask\9.0.242.0__89845dcd8080cc91\"
TreatWarningsAsErrors = "false"
WarningLevel = "1"
RootNamespace = "ScriptTask_8bd63ea4fc1f416f8d4f57f696fab4f0"
>
<Config
Name = "Debug"
DefineConstants = ""
DefineDebug = "true"
DefineTrace = "true"
DebugSymbols = "true"
RemoveIntegerChecks = "false"
/>
</Settings>
<References>
<Reference
Name = "System"
AssemblyName = "System"
/>
<Reference
Name = "System.Data"
AssemblyName = "System.Data"
/>
<Reference
Name = "Microsoft.SqlServer.ScriptTask"
AssemblyName = "Microsoft.SqlServer.ScriptTask"
/>
<Reference
Name = "Microsoft.SqlServer.ManagedDTS"
AssemblyName = "Microsoft.SqlServer.ManagedDTS"
/>
</References>
<Imports>
<Import Namespace = "Microsoft.VisualBasic" />
</Imports>
</Build>
<Files>
<Include>
<File
RelPath = "ScriptMain"
BuildAction = "Compile"
ItemType = "2"
/>
<VSAAppGlobal
VSAAppGlobalName = "Dts"
ItemType = "1"
VSAAppGlobalType = "Microsoft.SqlServer.Dts.Tasks.ScriptTask.ScriptObjectModel"
/>
</Include>
</Files>
<Folders>
<Include/>
</Folders>
</VisualBasic>
</VisualStudioProject>]]></ProjectItem></ScriptProject></DTS:ObjectData></DTS:Executable>
<DTS:Executable DTS:ExecutableType="Microsoft.SqlServer.Dts.Tasks.ScriptTask.ScriptTask, Microsoft.SqlServer.ScriptTask, Version=9.0.242.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91" DTS:ThreadHint="0"><DTS:Property DTS:Name="ExecutionLocation">0</DTS:Property><DTS:Property DTS:Name="ExecutionAddress"></DTS:Property><DTS:Property DTS:Name="TaskContact"></DTS:Property><DTS:Property DTS:Name="ForceExecValue">0</DTS:Property><DTS:Property DTS:Name="ExecValue" DTS:DataType="3">0</DTS:Property><DTS:Property DTS:Name="ForceExecutionResult">-1</DTS:Property><DTS:Property DTS:Name="Disabled">0</DTS:Property><DTS:Property DTS:Name="FailPackageOnFailure">0</DTS:Property><DTS:Property DTS:Name="FailParentOnFailure">1</DTS:Property><DTS:Property DTS:Name="MaxErrorCount">1</DTS:Property><DTS:Property DTS:Name="ISOLevel">1048576</DTS:Property><DTS:Property DTS:Name="LocaleID">-1</DTS:Property><DTS:Property DTS:Name="TransactionOption">1</DTS:Property><DTS:Property DTS:Name="DelayValidation">0</DTS:Property>
<DTS:LoggingOptions><DTS:Property DTS:Name="LoggingMode">0</DTS:Property><DTS:Property DTS:Name="FilterKind">1</DTS:Property><DTS:Property DTS:Name="EventFilter" DTS:DataType="8"></DTS:Property></DTS:LoggingOptions><DTS:Property DTS:Name="ObjectName">SCR Simulate Load</DTS:Property><DTS:Property DTS:Name="DTSID">{F1D5BC15-E01D-4EC9-B273-F7629DD5E478}</DTS:Property><DTS:Property DTS:Name="Description">Script Task</DTS:Property><DTS:Property DTS:Name="CreationName">Microsoft.SqlServer.Dts.Tasks.ScriptTask.ScriptTask, Microsoft.SqlServer.ScriptTask, Version=9.0.242.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91</DTS:Property><DTS:Property DTS:Name="DisableEventHandlers">0</DTS:Property><DTS:ObjectData><ScriptProject Name="ScriptTask_c345ea39a2e9477b9a56e3f3ae6a400f" Language="Microsoft Visual Basic .NET" EntryPoint="ScriptMain" SaveBinaries="False" ReadOnlyVariables="" ReadWriteVariables=""><ProjectItem Name="dts://Scripts/ScriptTask_c345ea39a2e9477b9a56e3f3ae6a400f/ScriptTask_c345ea39a2e9477b9a56e3f3ae6a400f.vsaproj">
<![CDATA[<VisualStudioProject>
<VisualBasic
Version = "8.0.50727.42"
MVID = "{00000000-0000-0000-0000-000000000000}"
ProjectType = "Local"
ProductVersion = "8.0.50727"
SchemaVersion = "2.0"
>
<Build>
<Settings
DefaultNamespace = "ScriptTask_c345ea39a2e9477b9a56e3f3ae6a400f"
OptionCompare = "0"
OptionExplicit = "1"
OptionStrict = "1"
ProjectName = "ScriptTask_c345ea39a2e9477b9a56e3f3ae6a400f"
ReferencePath = "C:\WINDOWS\assembly\GAC_MSIL\Microsoft.SqlServer.ManagedDTS\9.0.242.0__89845dcd8080cc91\;C:\WINDOWS\assembly\GAC_MSIL\Microsoft.SqlServer.ScriptTask\9.0.242.0__89845dcd8080cc91\"
TreatWarningsAsErrors = "false"
WarningLevel = "1"
RootNamespace = "ScriptTask_c345ea39a2e9477b9a56e3f3ae6a400f"
>
<Config
Name = "Debug"
DefineConstants = ""
DefineDebug = "true"
DefineTrace = "true"
DebugSymbols = "true"
RemoveIntegerChecks = "false"
/>
</Settings>
<References>
<Reference
Name = "System"
AssemblyName = "System"
/>
<Reference
Name = "System.Data"
AssemblyName = "System.Data"
/>
<Reference
Name = "Microsoft.SqlServer.ScriptTask"
AssemblyName = "Microsoft.SqlServer.ScriptTask"
/>
<Reference
Name = "Microsoft.SqlServer.ManagedDTS"
AssemblyName = "Microsoft.SqlServer.ManagedDTS"
/>
</References>
<Imports>
<Import Namespace = "Microsoft.VisualBasic" />
</Imports>
</Build>
<Files>
<Include>
<File
RelPath = "ScriptMain"
BuildAction = "Compile"
ItemType = "2"
/>
<VSAAppGlobal
VSAAppGlobalName = "Dts"
ItemType = "1"
VSAAppGlobalType = "Microsoft.SqlServer.Dts.Tasks.ScriptTask.ScriptObjectModel"
/>
</Include>
</Files>
<Folders>
<Include/>
</Folders>
</VisualBasic>
</VisualStudioProject>]]></ProjectItem><ProjectItem Name="dts://Scripts/ScriptTask_c345ea39a2e9477b9a56e3f3ae6a400f/ScriptMain.vsaitem">
<![CDATA[Imports System
Imports System.Data
Imports System.Math
Imports System.Security.Cryptography
Imports Microsoft.SqlServer.Dts.Runtime
Public Class ScriptMain
Private r As New RNGCryptoServiceProvider()
Private randomNumber(0) As Byte
Private randInt As Integer
Public Sub Main()
Dts.TaskResult = Dts.Results.Success
r.GetBytes(randomNumber)
randInt = Convert.ToInt32(randomNumber(0))
If randInt Mod 2 = 0 Then
Dts.TaskResult = Dts.Results.Failure
End If
End Sub
End Class]]></ProjectItem></ScriptProject></DTS:ObjectData></DTS:Executable>
<DTS:PrecedenceConstraint><DTS:Property DTS:Name="Value">0</DTS:Property><DTS:Property DTS:Name="EvalOp">2</DTS:Property><DTS:Property DTS:Name="LogicalAnd">-1</DTS:Property><DTS:Property DTS:Name="Expression"></DTS:Property><DTS:Executable IDREF="{F4FE90C9-C8DE-4B40-8A6D-63EAE102B563}" DTS:IsFrom="-1"/><DTS:Executable IDREF="{F1D5BC15-E01D-4EC9-B273-F7629DD5E478}" DTS:IsFrom="0"/><DTS:Property DTS:Name="ObjectName">Constraint</DTS:Property><DTS:Property DTS:Name="DTSID">{EF54EA09-00A5-40A0-B192-78EF3F7E731C}</DTS:Property><DTS:Property DTS:Name="Description"></DTS:Property><DTS:Property DTS:Name="CreationName"></DTS:Property></DTS:PrecedenceConstraint><DTS:Property DTS:Name="ObjectName">SEQ Atomic</DTS:Property><DTS:Property DTS:Name="DTSID">{39E92202-B557-4E08-9155-6ABAC07535BE}</DTS:Property><DTS:Property DTS:Name="Description">Sequence Container</DTS:Property><DTS:Property DTS:Name="CreationName">STOCK:SEQUENCE</DTS:Property><DTS:Property DTS:Name="DisableEventHandlers">0</DTS:Property></DTS:Executable><DTS:Property DTS:Name="ObjectName">Package3</DTS:Property><DTS:Property DTS:Name="DTSID">{22A10402-108C-4F32-89B3-EBD3C48990D5}</DTS:Property><DTS:Property DTS:Name="Description"></DTS:Property><DTS:Property DTS:Name="CreationName">MSDTS.Package.1</DTS:Property><DTS:Property DTS:Name="DisableEventHandlers">0</DTS:Property></DTS:Executable>