Back in the day, we'd build error handling into DTS packages by taking the onerror path out of tasks and the like. I guess it was possible even back then to hook the onerror events, but I never tried. So, now that SSIS makes it easy to add event handlers, is there any new best practice for managing package behavior when a major task has an error? A little Googling and checking books around the office, turns up little or nothing on the topic. Thanks. Josh

yes this is what is do http://plexussql.blogspot.com/2010/01/emailing-error-log-file-as-attachments.htmlSincerely SH -- MVP, MCITP 2008, MCTS 2008 & 2005 -- Please kindly mark the post(s) that answered your question and/or vote for the post(s)

Like most things, the answer to this will almost certainly be "It Depends". What would you do with the data exceptions? How would you like to handle tasks failing? For task failures, it is usually the case that you would fully log these errors and send out notifications in the OnError event handler. However, you can also collect the full stack of errors and report on them in one notification through the use of the OnError and OnPostExecute handlers. See John Welch's Handling Multiple Errors in SSIS Revisited . You can also use the Failure Execution Result (Red Line) after a task to continue processing if an error is encountered. In the data flow, you can build your packages to push data exceptions from the error outputs of the various sources, transforms and destinations into a data exception file. However, it really isn't sufficient to simply push the records to this file without having some process in place to review the exceptions and reintegrate them into the system, or correct the data at the source and re-import.Please mark answered posts. Thanks for your time.

I’d suggest that it is so much easier to just user regular logging to capture the error information. For a belt and braces approach use built-in SSIS logging, and also capture the output of the execution host and log that as well. The particular example of having tasks inside a package to send emails is quite frankly painful. Never mind the issue that On Error events fire multiple times, it is just a high overhead, firstly to implement consistently, and secondly to maintain. Changing n tasks in 100 packages if you decide to improve your custom scheme is not my idea of good design. That point can be mitigated to some degree by implementing your logic via a custom task, but that is a step too far for many people, especially when they start designing their “framework”. There are still plenty of specific instances when task level error handling can be useful, but I would not advocate it as a starting point, or the general rule. I think use the logging as a start point, and then augment it as required with custom logging, event handlers, on error constraints etc DTS didn’t have events, and the logging was so shocking it made sense to add your own.http://www.sqlis.com | http://www.konesans.com

I found a way to maintain custom error handling in a central location. I use a separate package that is called from the event handler. The child package gets the system error information using package configurations. I also created a separate package to handle logging to files using the same mechanism.

Using a child package makes sense for encapsulation, but there is quite an overhead to starting a package, resources and also time. That will effectively block the parent package, as event handlers are synchronous. Therefore you may wish to be carefull how often your package gets called, as it could have serious performance impact. As an aside events themselves are not ideal for performance, certainly don't fire them yourself too often. For pure encapsulation a custom task may be a better option than a package. It is single point of maintenance as well.http://www.sqlis.com | http://www.konesans.com