How to fail SSIS component on invalid rows

I want to be able to do the following:

• Import a flatfile row by row
• Do a conditional split on a column value of each row
• if i detect a valid value, i want to continue with this row
• if in any case i detect a value that is not possible i want to FAIL the component so the workflow stops and i 开发者_运维问答get an error

Example: I have a file with three columns ( "type", "value", "message")

I want to import that file row by row using the flatfile destination. Then i want to do the conditional split. If the "type" column has "1" as value, i want to write that row into my destinatoin. If the type is "2" or "3" i want to ignore them. However if its "A" or "0" i want to FAIL the component.

I have everything except the "FAIL" part. I configured the output for the last condition ("column is not 1,2 or 3") as "Fail Component", however it does not actually fail the component.

---

Add a script component to the data flow (as destination) and link it to your conditional split for the condition you want to force a package fail.

inside the script component add

    bool pbCancel = false;
    this.ComponentMetaData.FireError(100, "Script Component", "Data Error <type message here>", "", 0, out pbCancel);

The script component itself will succeed, but the data flow task will fail and should result in a package failure

---

I just would add something that I searched for a while and finally found alone...

Like Daryl said, if you just use the ComponentMetaData.FireError() method, you will see the errors in the execution result but the script component won't fail. The data flow task will fail but only at the end, after doing the job (so maybe after some INSERT and/or UPDATE). If you want to completely stop the data flow execution, you can do like this :

• Create a conditional split on your failure condition with 2 outputs:
  1. Lines OK
  2. Lines that don't meet the condition
• On the 2nd Output, run the script that fire the error with an clear message for each line. The script component must be a transformation, not a destination, to have an output.
• After the script component, add an OLE DB Command that raise an SQL error on your destination base : RAISERROR(N'At least one line not configured have been found',16,1)
• Finally, after the OLE DB Command use a Union All component to join the 1st output of the conditional split (line OK) with the output of the OLE DB command.

This way, you'll be able to raise an error for each line that doesn't fulfill a condition and stop the data flow task before inserted records if at least one error occurs.