Redirect rows with errors from Flat File Destination

Question

I have a SSIS package with the following:

1. An OLE DB Source that pulls data from a table via a SQL command
2. A Flat File Destination that stores the data extracted from DB in CSV format
3. A File Connection Manager, for the Flat File Destination, that is configured to use Code Page "1252 (ANSI - Latin I)"

In the source table there is some data that contains characters that are not valid for the 1252 code page. When the package tries to insert this data in the destination file it throws an error in the Flat File Destination step.

I want to redirect the rows with invalid characters to a separate file. If the error was happening in the Source OLE DB component I could use the error path with "Redirect Rows" configuration. However, because the error is in the Flat File Destination component and this component has no outputs I'm unable to capture those rows. How should this be done?

Answer 1

You can insert a Data Conversion component before Flat File destination, convert your data field to some 'copy of ...' column and configure Data Conversion error output. This allows you to capture erroneous rows together with its error code.
Please note that such design effectively filters out erroneous rows from normal DataFlow path by redirecting to Error Output.