During an SSIS package execution, how are SQL database errors handled? Give an example of a handled and unhandled error event in a well designed package.

Error events are handled through the Event handler. Each task and container may have a dedicated ON-ERROR event defined for it.  When an error occurs, the event handler is immediately invoked for the event defined at the failing task level.  ON-ERROR events defined within a data flow (red line to a defined task) may be configured to not fail the load and execute any defined tasks prior to going to the next row.  If no error-handling event is defined within a data flow or the error occurs in the processes flow, control is transferred to the next highest level of the package (ie. The container level).  If an ON-ERROR event is defined there, that code is executed, then control is raised up the chain until the package ON-EVENT is reached.  Once control leaves a data flow for an ON-ERROR event, the package will not return to the data flow.