I am guessing you are running your package using the Windows Scheduler using dtexec, in this case the setting "Fail Package on Failure" is applicable to each component that executes SQL.Arthur My Blog

Thanks for the reply. We are running the package through a scheduled SQL Server Agent job. I don't mind setting the "FailPackageOnFailure" for each SQL task, but I'd like to ask a follow-up: Is it possible to set a property at the package level that will cause the package to fail if any SQL Task fails? Is it true that only SQL Tasks need to have the "FailPackageOnFailure" set True? Will a failure in a non-SQL task cause the package to report failure? Thanks, BCB

Greetings, I have a package that fails because one the the SQL steps attempts to insert a duplicate row. The package fires the package-level OnError event and it then terminates, but it reports a successful outcome and not the failure I expect. The messages below are what appeared in the "View History" window for the execution that failed. What do I need to do to make the package report its true termination state - failure? All tasks in the package should succeed or the package should report failure. Do I need to mark each separate task as "Fail Package on Failure"? I'd like to handle this at the package level. If any task fails, the package fails. Can this be accomplished by setting the "FailParentOnFailure" property True? What is the parent of an SSIS package? Would it be the agent job executing the package? Thanks, BCB Executed as user: MYDOM\xxx. Microsoft (R) SQL Server Execute Package Utility Version 10.0.2531.0 for 64-bit Copyright (C) Microsoft Corp 1984-2005. All rights reserved. Started: 10:55:48 AM Error: 2011-01-17 11:42:45.44 Code: 0x00000000 Source: Prepare LocationCoverage fact table Description: Violation of PRIMARY KEY constraint 'PK_FactLocationCoverage'. Cannot insert duplicate key in object 'dbo.FactLocationCoverage'. End Error Error: 2011-01-17 11:42:45.51 Code: 0xC002F210 Source: Prepare LocationCoverage fact table Execute SQL Task Description: Executing the query "EXEC dbo.usp_PrepareFact_LocationCoverage ;" failed with the following error: "The statement has been terminated.". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly. End Error DTExec: The package execution returned DTSER_SUCCESS (0). Started: 10:55:48 AM Finished: 11:42:45 AM Elapsed: 2816.84 seconds. The package executed successfully. The step succeeded.