Some months ago we began developing a simple way to log the lookup errors in our SSIS solution.
First the following structure was created:
As you can see the main pont of this method are the Multicast (to
direct error rows into a log table (OLE DB Destination) and
simultaneously back into the main data flow) and the Union All tasks.
With
many lookups it looks scary and to our surprise did not work. I browsed
google for hours, asked other guys in forums, ... but no solution :-(
Because
there was no error message only the package stops with some taskboxes
being green, some yellow and some transparent. The transferred row
counts were visible between certain tasks but with values get lower and
lower when browsing the package downwards.
The solution came
weeks later when dealing with a whole different issue! As I didn't
mentioned but could have been thought there were many OLE DB
destinations (one for each lookup task) and hence there coulde have
been many errors that should be transferred into the same table so it
became a locking conflict with zero warning (even after hours of
waiting).
The way how we solved it was to create a Union all to
union every green lookup error arrow and redirect the data into one OLE
DB Destination task like this:
After finding this solution the problem does not seem so difficult but it caused me a lot of headache.
Would any of you have know the solution at sight?