My solution deals with ETL process logging and SSIS. Because I'm an ETL person.

I created an audit table that included the package name, the instance ID, the start events (I love me some containers), the error criticality, the row counts, the end events, and on error event handling that included the entire error message.

I used a stored procedure to create the audit records but now that SSIS has evolved a bit I would look into using more of the event handlers. The downside to this is letting other developers know that the event handlers exist.

What we got out of this was run durations for various portions of the package, the run duration of the packages, and the amount of data being moved around. If we were expecting 3,000 records and got 300,000 we knew something went wrong. Ditto with getting 0 records.

The intention was to integrate a data driven SharePoint distribution based on the creation of a critical event. We never got that far and made the audit log a regular part of checklists.

It worked great and gave us a lot of insight into the functioning of our processes and when something did a faceplant without generating an error.