How to force an SSIS Package to complete without reporting any errors?

Ok! First off, let’s do a quick review of SSIS! 

• Microsoft SSIS (SQL Server Integration Services) is just one of the database tools provided by Microsoft within SQL Server Data Tools (SSDT) –as we know call it in SQL 2012.
• SSDT is a special edition of Visual Studio, Microsoft’s principal integrated development environment (IDE), which supports database development projects such asr SSIS projects, SSAS and Data mining projects.

• On SQL Server 2012 (or greater) setup,

• The SSIS platform offers a graphical development interface for ETL Techniques (data integration, data transformation and data migration) allowing you to assemble “connections, control flow elements, data flow elements, event handlers, variables, parameters, and configurations” into compiled packages: SSIS packages. You would then run these packages to perform ETL and other data processes.
• When you open up SSDT, choose New –> Project –>“Integration Services Project”

• 

SSIS Packages are made of “executables” and “non-executables.”

• “Executables” are components within the package able to either call or start an activity.
  • Example of “executables” are:
    • SSIS Containers:
      • Sequence Container,
      • ForEach & For Loops
      • and also TaskHosts (which contain Tasks such as the ExecuteSQL Task and Data Flow).
    • A Package itself is an “executable” since it’s able to call or start an activity.
    • NOTE:
      • Data Flow sources, transformations and destination are not The Data Flow Task is – because it can start the Data Flow – but you cannot in isolation call a Derived Column Transformation, for example.
      • Resource: (http://www.bimonkey.com/2010/10/understanding-the-ssis-object-model-part-1-executables/)

• SSIS packages have these other components:
  
  • Connections
  • Event Handlers
    • Note that Event Handlers let you run SSIS components on a per-executable, per-event basis. For example, suppose your package includes a Foreach Loop You can associate one or more SSIS components with each event generated by that container when it is executed. This includes such events as OnError, OnInformation, and OnPostExecute.
  • Parameters (SQL 2012)
  • Precedence constraints
  • Tasks
  • Variables
  • Resource: https://en.m.wikipedia.org/wiki/SQL_Server_Integration_Services

 

Executables have properties that dictate their behavior:

• “executables” have properties that dictate their default behavior as to whether they fail on error or whether the fail the entire package on error.

• An SSIS Package itself has a number of properties, and these properties have also default values. A default behavior, for instance, is for the package to fail if any of its components fail. Please see how the following properties (on SSIS package-components) dictate this behavior:

SSIS Package Property	Default Value	Explanation
DisableEventHandlers	False	Specifies whether the package event handlers run.
FailPackageOnFailure	False	Specifies whether the package fails if an error occurs in a package component. (“False” is the only valid value of this property)
FailParentOnError	False	Specifies whether the parent container fails if an error occurs in a child container.
MaxConcurrentExecutables	-1	The number of executable files that the package can run concurrently. (Value “-1” indicates that there is no limit).
MaximumErrorCount	1	The maximum number of errors that can occur before a package stops running.
ForcedExecutionValue	1	If ForceExecutionValue is set to True, a value that specifies the optional execution value that the package returns.

Resource: https://msdn.microsoft.com/en-us/library/ms137749.aspx

So, why would you want to override the default behavior in an SSIS package:

• You are anticipating to find errors in your data flow.
  • That is, a failed component doesn’t mean the sky is falling, and you wish the SSIS package to continue executing even after an error is encountered:
  • Connection might get lost and restored during package execution.
  • You might be looping through a number of SQL servers, and Net Ops forgot to tell you that they decided to simply take one out.
• Oh yeah, while certain package-components might fail, the data you are getting is still important and so the SSIS package must reach its end.

1)Ready? You override the package’s default behavior and allow it ignore errors by following 3 easy steps:

1)    If you wish the package to complete with a “success” state, modify default behavior at the package level.

2) If you wish “executables” to complete with a “success” state regardless of errors found:

1. On the “executable,”
   1. set property “Max Error Count” to 0
   2. Set property “ForceExecutionResult” to “Success”
      1. For “executable” to finish as “success,” property “Max Error Count” must be set to 0 (in my experience).
      2. Note that on a container, property “max error count” ignores any propagation settings set on its child items.
         1. It lets you define the number of errors that are allowed to occur in any child item before the container itself fails.
         2. If you wish for the container to fail after 10 child items have failed, you would set “Max Error Count” to 10.

Before:

After:

 

3) If you wish to “handle” the error event in a way of logging the event or performing another function, Create an “error event handler” on the SSIS executable, so that when an error is encountered, the error is handled by the “Error event handler.”

• While working on the “executable,” choosing the event handler tab would expose a number of variables under the variable panel; one of them is the “Propagation” variable. Set this variable to “False” meaning you don’t wish the “failure” to propagate to a higher level event handler.
• Resource: https://msdn.microsoft.com/en-us/library/ms141788.aspx

1. Note: Please note that not every object (in an SSIS package) has a “Propagate” event.
   1. As mentioned before, SSIS connection components don’t have an “event handler” so you won’t find property “Propagate” in them.

1. If you are executing another package, within your package, and thechild package fails, the “failure” event will still be propagated to the parent package unless you set the DisableEventHandlers property of the Execute (parent) Package task to True.

Resource: https://connect.microsoft.com/SQLServer/feedback/details/486780/ssis-propagate-property-has-no-effect-on-parent-packages