SSIS - Error outputs should you configure them
Should you connect configure error outputs for your
components in a data flow? I'm putting a SSIS crib sheet together I asked Jamie
Thomson to have a look over it and one point he question was my statement on
error outputs.
My view is that you should connect up error outputs to a destination to
capture erroneous data. His argument is that he would prefer the package to fail
(if you handle the errors the data flow won't fail) if you get errors.
I suppose its two different mindsets, I come from an operational background
where I know first hand that the cost of something failing is very high, mainly
from a disruption factor but also because until you fix it, it doesn't work and
more than likely the subsequent processes won't work. If you've got change
control processes and tight development resource it might take some time before
a fix is available. I'm therefore of the view that having something is better
than nothing.
Further more configuring the error output to redirect rows allows you to
easily debug what is causing an error. You could still put a rowcount transform
on the error output and only proceed if the rowcount is 0, at least that way you
have the erroneous data and some error codes.
-