SQL Server Integration Services - Redirecting failing records to flat file destination

Observe the following snippet of an SSIS package:

Both of the Flat File Destination tasks on the right are configured to write to the same Flat File Connection Manager, because I want all the failing records to be redirected to the s开发者_如何学编程ame file regardless of which task caused them to fail.

SSIS is complaining on the second Flat File Destination, saying that the output file is in use by another process.

Is my stated goal simply impossible, or is there a way to redirect all failing records to the same output file?

No, you cannot do it this way. You need to use an Union All to combine the output before writing the output from different sources/transformations to the same destination.

If you have a process flow as described in the question, the package will fail on the second destination component.

The error message would be `The process cannot access the file because it is being used by another process.

To fix the issue, add a Union All transformation that will take the error output from Derived Column transformation and combines it with the error output from OLE DB Destination and the output of Union All transformation is then passed on to the Flat File destination.

Hope that helps.