Multicast error

Some months ago we began developing a simple way to log the lookup errors in our SSIS solution.

First the following structure was created:

Multicast error logging - wrong!

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:

Multicast error - corrected

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?

Published Thursday, December 4, 2008 11:46 AM by MartinIsti
Filed under: ,

Comments

# re: Multicast error

Thursday, December 4, 2008 12:24 PM by jamiet

The pictures aren't displaying. Could you correct that please?

# re: Multicast error

Thursday, December 4, 2008 1:52 PM by MartinIsti

I've corrected the picture layout.

And since this is my first real post would you share me your opinion?

# re: Multicast error

Sunday, December 7, 2008 6:02 PM by Miky Schreiber

It seems that you had some kind of a deadlock over there.

As a best practice, an ETL process should not have more than one target for each table. This takes for every ETL tool, not only for SSIS. Also, if you have some targets to tables with relationships between them you need to be extra careful (try not to get to that situation at all).

# re: Multicast error

Sunday, December 7, 2008 7:45 PM by MartinIsti

Yes, you're right. The tricky part was that I received no error messages and the package just stopped without any shown reason.

It was the first SSIS solution I had to manage and another guy created it so it was hard to find the correct clue.

At least I had a chance to learn from my mistake :)

Powered by Community Server (Commercial Edition), by Telligent Systems