Case
I used the
propagate variable trick to continue a loop on error. That works within the package, but the parent package calling the package with the loop still fails.
 |
| Child succeeds, parent fails |
Solution
This is
by design according Microsoft. The workaround they suggest is setting the DisableEventHandlers property of the Execute Package task to True. This should ignore
all errors in the child package. Below here is an alternative solution to only ignore
expected errors. Don't hesitate to post your own solution in the comments.
A parent package variable will be filled by the child package in case of an unexpected error. In the parent package it will be used to throw an error when it's filled.
1) Variable - Parent Package
Add a string variable in the
parent package called childError.
 |
| Add string variable |
2) OnError - Child Package
Go to the
child package where you used the propagate variable 'trick' and add an OnError event handler on package level. Go to Event Handlers tab. Make sure the package is selected as Executable and OnError as Event handler. Then click on the link in the middle of the page:
Click here to create an 'OnError' event handler for the executable 'Child1'.
 |
| OnError event handler for package |
3a) Script Task - Child Package
Add a Script Task to the event handler and give it a suitable name. Then edit it and add the System variables ErrorCode and ErrorDescription as read only variables and type the name of the parent package in the read write box. You can't select it because it's only known at runtime.
 |
| Add the variables |
3b) The Script
Hit the edit button and copy the code of my main method to your main method. The example code is in C#. For a VB.Net version you can use this
conversion tool.
// C# Code
using System;
using System.Data;
using Microsoft.SqlServer.Dts.Runtime;
using System.Windows.Forms;
namespace ST_ace2311e5a4c4bbb98101cd54888c7c9
{
[Microsoft.SqlServer.Dts.Tasks.ScriptTask.SSISScriptTaskEntryPointAttribute]
public partial class ScriptMain : Microsoft.SqlServer.Dts.Tasks.ScriptTask.VSTARTScriptObjectModelBase
{
public void Main()
{
// Fill parent package variables with error message from this child package.
Dts.Variables["User::ChildError"].Value = Dts.Variables["System::ErrorCode"].Value.ToString() + " - " + Dts.Variables["System::ErrorDescription"].Value.ToString();
Dts.TaskResult = (int)ScriptResults.Success;
}
#region ScriptResults declaration
///
/// This enum provides a convenient shorthand within the scope of this class for setting the
/// result of the script.
///
/// This code was generated automatically.
///
enum ScriptResults
{
Success = Microsoft.SqlServer.Dts.Runtime.DTSExecResult.Success,
Failure = Microsoft.SqlServer.Dts.Runtime.DTSExecResult.Failure
};
#endregion
}
}
4) Propagate - Parent Package
Go back to the
parent package and add an empty event handler on the Execute Package Task so that it won't fail if the child package has an error. Go to the event handlers. Select the Execute Package Task as executable and select OnError as eventhandler and then create the event handler by clicking on the link in the middle of the screen: "Click here to create an 'OnError' event handler for executable 'EPT - Child1'.
Next go to the variables pane and hit the Variable Grid Option button to also show system variables (SSIS 2008 has a different button). Then find the system variable Propagate and set it to false.
Last step is to add an annotation in the empty event handler to explain why it's empty.
 |
| OnError Event Handler |
 |
| Show system variables for SSIS 2008 |
5a) Script Task - Parent Package
Go back to the Control Flow of your
parent package and add a Script Task to fire an error. Connect it to the Execute Package Task with an expression only on the precedence constraint:
@[User::ChildError] != ""
 |
| Fire Child Error |
5b) The Script
Edit the Script Task and add the string variable ChildError as read-only variable. After that hit the edit button and copy the content of my main method to your main method. The example code is in C#. For a VB.Net version you can use this
conversion tool.
 |
| Read-only variable |
// C# Code
using System;
using System.Data;
using Microsoft.SqlServer.Dts.Runtime;
using System.Windows.Forms;
namespace ST_7b522ca79c9f4428a233a100bfc66e6e
{
[Microsoft.SqlServer.Dts.Tasks.ScriptTask.SSISScriptTaskEntryPointAttribute]
public partial class ScriptMain : Microsoft.SqlServer.Dts.Tasks.ScriptTask.VSTARTScriptObjectModelBase
{
public void Main()
{
// Fire error with error message from child package and fail script task
Dts.Events.FireError(0, "Child package", "Child package error: " + Dts.Variables["User::ChildError"].Value.ToString(), string.Empty, 0);
Dts.TaskResult = (int)ScriptResults.Failure;
}
#region ScriptResults declaration
///
/// This enum provides a convenient shorthand within the scope of this class for setting the
/// result of the script.
///
/// This code was generated automatically.
///
enum ScriptResults
{
Success = Microsoft.SqlServer.Dts.Runtime.DTSExecResult.Success,
Failure = Microsoft.SqlServer.Dts.Runtime.DTSExecResult.Failure
};
#endregion
}
}
6) Precedence Contraint - Parent Package
If there is no unexpected error in the child package then we continue with the next task(s). Each task that is connected to the Execute Package Task with a Success Precedence Constraint should be changed to an expression:
@[User::ChildError] == ""
 |
| Continue if there are no unexpected errors in the child package |
7) The result
I added a Script Task in the child package that always fails to simulate an unexpected error. On the second run there where only expected errors in the loop, so the child package was successful. In the parent package there is still a red cross, but it continues without (unexpected) errors.
 |
| The result |
