Use this method when you need to force certain tasks to succeed in spite of failure scenarios. This applies to spreadsheets and flat files loads that are non-critical to the remaining flow of tasks and should not cause the entire job to fail. This method involves moving desired load tasks into a new sub package and calling the sub package from parent via Execute Package Task. A working example is set up on TEST server for reference.
The set up and configuration is as follows ->
In this example we will move the Flat File load Task to a new Sub-package.dtsx that will in turn be called from Execute Package Task.
Double Click on Execute Package Task à Package à ReferenceType. Change it to “External Reference”, the reason being that when the parent package is executed from SQL Agent job the child package will be accessible and be able to run. Skipping this step will result in an error shown below.
After switching to External Reference, you’ll need to specify a connection from where the SQL Agent job will run.
After selecting the connection, select your sub package containing file loads.
Go back to the parent package, highlight “Execute Package Task” and open up Properties for that Task. Change the property value of “ForceExecutionResult” from “None” to “Success”. Make sure you don’t accidently set this for entire package!
Click on “Event Handlers” tab, and select “Execute package task” from the drop down list.
Click on the blue colored link as seen on the above screenshot. This will create an “OnError” event for “Execute Package Task” and thereby exposing new system variables that are otherwise not accessible. Make sure to click on grid option icon and select “Show system variables”.
Scroll down and locate “Propagate” variable and change it to False from the default value of True. This will ensure that any error that occurs in that component (Execute Package task in this case) will not propagate to the other elements of control flow. Make sure this is done ONLY on the OnError Event of “Execute Package Task”.
Add a “Send Mail Task” with an On Failure path of the precedence constraint. This will send an email notifying the intended recipient of sub-package failure.