Getting error context from executed package

Background:

I am executing a package programmatically from ASP.NET using C#. The package is being loaded from SQL Server at runtime and then it is executed from a custom class written in C#.

Problem:

When a task fails, I'd like information on why it failed. I read about implemeting the IDTSEvents interface as a way to have my package call back to code. This works fine (I have code in the OnTaskFailed event handler), but I'm not sure how to find out why a task failed. Since my task is loading a flat file to SQL Server, failure will likely come in one of two flavors: either the file format will be wrong or the data will violate a database constraint. In etiher case, I'd like that feedback. I am looking that the TaskHost object that gets passed to my event handler and I haven't figured out how to access this information. Am I going about this the right way? If yes, how do I access the error information I am looking for?

Try the OnError event handler. There are variables scoped to that eventhandler that contain the error code and error description.

-Jamie

|||

OK. That works fine. I can now get the description of the errror. When I get an error, I'd like to stop executing the package and surface the error to the through the UI (which happens to be a web page). There are a couple of things I noticed with this call back to the OnError event handler:

The event handler is triggered multiple times even though I return false, which is supposed to cancel the task. What I'd like is to abort the execution of the package (not just cancel the task). I tried to throw a custom exception within the handler, but the exception doesn't propogate from the OnError handler to my calling code (the code that executes the package) and it doesn't stop the handler from being called multiple times.

Any ideas?