What are the advantage of Event Handling in SSIS ?. What are the different ways to implement that ?.

hope this helps u http://technet.microsoft.com/en-us/library/cc304422(TechNet.10).aspx From anlis

Handling Package Errors with Event Handlers In the data flow, using data viewers provides the ability to easily debug problems while processing data. The control flow, however, is different because the focus is on workflow and execution rather than on data and transformations. Leveraging the capabilities in Visual Studio, the control flow supports visual debugging and breakpoint features. Let's first look at the event handlers that SSIS provides; then we will explore the debugging capabilities in the control flow. Event Handlers SSIS provides the ability to listen for certain execution events and perform other operations when an event happens (depending on the execution event). For example, if an error happens, the error event handler can send an alert or potentially fix a data problem. Event handlers use the control flow paradigm for workflow processing, which includes all the same control flow tasks and containers that are found in the toolbox of the control flow. You can define zero, one, or more than one event handler for a package. To add an event handler to a package, you need to select the Event Handler tab in the package designer. Creating a new package event handler requires that you select the executable and the event handler event, as Figure 5-11 shows. Figure 5-11 Selecting the executable and event handler event for a package The executable is the task or container scope that the event will fire. You can also choose the package itself (the highest-level container) as the executable for an event. The event handler event is the actual event that causes the event workflow to execute. The following table includes the package event handler types: In addition, event handlers assigned to an executable scope will propagate down to child events when the event fires. If an event is assigned to a container, the child executables include the tasks and containers that are embedded within the parent container. This means that if you assign an OnError event to the package and an OnError event occurs at a task, the event handler would fire for both the task and the package (and for any containers in between). You would use an event handler for tracking error details, for sending failure messages in emails, and for implementing manual rollback logic. BEST PRACTICES Capturing error information with the OnError event Each package contains a set of system variables that are updated for the various levels in the package during the package execution. With event handlers, you can capture these variables and values, which provide contextual information, such as the ErrorCode, ErrorDescription, and SourceName (the task), when the event fires. Using event handlers is a great way to track package execution; they can be used to audit the execution, capturing the errors that occur in a task. In addition, the event handler Send Mail Task can be used for notification; for example, it can notify an administrator of a certain predefined condition that requires a special response. Exam Tip Event handlers can be turned off for any task or container by setting the Disable-EventHandlers property of the Task or Container to True. In other words, if you have an event handler defined, but you specifically do not want it to be invoked for a specific task, then you can turn off event handlers for that task only.

Hi, This Will Help You. http://www.accelebrate.com/sql_training/ssis_2008_tutorial.htm 1. How do you do Error Handling in SSIS? By Using Event Handler, we can track package execution. At run time, executables (packages and Foreach Loop, For Loop, Sequence, and task host containers) raise events. For example, an “OnError” event is raised when an error occurs. Using event handlers is a great way to track package execution; they can be used to audit the execution, capturing the errors that occur in a task. In addition, the event handler Send Mail Task can be used for notification; for example, it can notify an administrator of a certain predefined condition that requires a special response. Event handlers are defined on the Event Handlers tab of the Package Designer. When you create an event handler, you handle the event by building an entire secondary SSIS package, and you have access to the full complement of data flows, control flows, and event handlers to deal with the original event. Event Handler Content: Creating an event handler is similar to building a package; an event handler has tasks and containers, which are sequenced into a control flow and an event handler, can also include data flows. The SSIS Designer includes the Event Handlers tab for creating custom event handlers. Ø Run-Time Events: The following table lists the event handlers that Integration Services provides, and describes the run-time events that cause the event handler to run. Event handler Event OnError Ø The event handler for the OnError event. This event is raised by an executable when an error occurs. OnExecStatusChanged Ø The event handler for the OnExecStatusChanged event. This event is raised by an executable when its execution status changes. OnInformation Ø The event handler for the OnInformation event. This event is raised during the validation and execution of an executable to report information. This event conveys information only, no errors or warnings. OnPostExecute Ø The event handler for the OnPostExecute event. This event is raised by an executable immediately after it has finished running. OnPostValidate Ø The event handler for the OnPostValidate event. This event is raised by an executable when its validation is finished. OnPreExecute Ø The event handler for the OnPreExecute event. This event is raised by an executable immediately before it runs. OnPreValidate Ø The event handler for the OnPreValidate event. This event is raised by an executable when its validation starts. OnProgress Ø The event handler for the OnProgress event. This event is raised by an executable when measurable progress is made by the executable. OnQueryCancel Ø The event handler for the OnQueryCancel event. This event is raised by an executable to determine whether it should stop running. OnTaskFailed Ø The event handler for the OnTaskFailed event. This event is raised by a task when it fails. OnVariableValueChanged Ø The event handler for the OnVariableValueChanged event. This event is raised by an executable when the value of a variable changes. The event is raised by the executable on which the variable is defined. This event is not raised if you set the RaiseChangeEvent property for the variable to False. For more information, see Integration Services Variables. OnWarning Ø The event handler for the OnWarning event. This event is raised by an executable when a warning occurs. Thanks Shiven:) If Answer is Helpful, Please Vote