What´s the difference between Error output and “No

问题:

I want to use a lookup transformation in SSIS and connect it to two flat file destinations. I know there are the two green outputs from the transformation but couldn´t i use the red error output instead of "No match output" and "Redirect row" instead? What´s the difference?

回答1:

With the Lookup Transformation in SSIS, you have control over how you want to handle "No Match" situations. Double-click your Lookup Transformation and on the "General" tab you can select how to handle non-matching rows, by selecting one of the following options:

• Ignore Failure: Continues processing as if nothing had happened
• Fail Component: Throws an exception and stops processing the Data Flow Task
• Redirect Rows to Error Output: Rather than following the green output, moves the row to the red output to be handled separately.
• Redirect rows to no match output: Switches the row to a secondary output, allowing you to handle non-matching data differently to matching data.

If you right-click your Lookup and select "Show Advanced Editor", you can see a bit more detail. Jump over to the "Input and Output Properties" pane and you can see the difference between your "Lookup No Match Output" and "Lookup Error Output" streams.

The "Lookup Error Output" is a standard and non-editable output stream that catches the error and adds error details to the existing column collection, allowing you to handle the error, log it, track the row that caused it, etc.

The "Lookup No Match Output" allows you to define your own columns to pass to a different output stream and/or do some different processing to rows that fail to match. A good example of this is using a "Lookup Transformation" to see whether you need to add new rows to a lookup table in the database while processing. Rows that don't match could be redirected to the "No Match Output", grouped to remove duplicates, and inserted to the database, while the rest of the rows continue onwards.

Bottom line, it depends what you're trying to do. If you want to handle errors (i.e. truncation, differently to how you would handle non-matching rows, then you can use the "Lookup No Match Output" to do so.

Personally, I would rather handle the known condition using Match/No-Match outputs and save the "Error Output" for actual unexpected exception handling.