SQL server 2005 Integration services provide the ability to handle any type of event associated with execution of its task and container (through the ability to configure corresponding handlers)
The following last contains more significant and commonly monitored types of events (you might be able to spot some of them in the Output window during package execution in Debug node)
- On Error :-
Generated as the result of on error condition. It falls into the category of the most frequently implemented types of event handler. Its purpose can be for additional information simplifying trouble shooting, or to notify about a problem and need for remediation.
- On Warning:-
Similar to the On error event, it is raised in response to a problem (although not as significant in terms of severity).
- On Information:-
Produces reporting information relating to the outcome of either validation or execution of a task or container (other than warning or error)
- On –Task Failed:-
Signals the failure of a task and typically follows on error event.
- On Pre Execute:-
Indicates that an exec table component is about to be launched.
- On Pre validate:_
Marks the beginning of the component validation stage, following the on pre execute event. The main purpose of validation is detection of potential problems that might prevent execution from completing successfully.
Desired to gain proficiency on MSBI? Explore the blog post on MSBI Training Online to become a pro in MSBI.
- On post validate:-
Occurs as soon as the validation process of the component is completed ( following on prevail date event),
- On post Execute:
Takes place after an executable component finishes running
- On variable value changed:
Allows you to detect changes to variables. The scope of the variable determines which executable will raise the event. In addition, in order for the event to take place, the variable’s change event property must be set to true (the default is faces)
- On progress:
Raised at the point where measurable progress is made by the executable (for example, when running execute SQL Task).
- This can be evaluated by monitoring the values of the system variables associated with the On progress event handler, such as progress completes Progress count low, and progress count high.
Steps to configure Event handler
Scenario: Clean up or truncate the destination table before executing or loading data into destination.
Open Business Intelligence Development studio ————->
Create a new package and Rename it as Event Handler.dtsx ————->
In control flow drag and drop data flow task and Rename it as DFT Event Handler ————->
In data flow drag and drop OLEDB source ————->
Double click on OLEDB source to edit it ————->
Provide connection manager if exists ————->
Select [human resource] . [Employee] table from the dropdown list ————->
Select columns from left panel ————->
Check out the top MSBI Interview Questions now!
Click OK ————->
Drag and drop OLEDB destination and make a connection from OLEDB source to destination ————->
Double click on OLEDB destination ————->
Provide destination connection manager ————->
Click new to create a destination table and Rename OLEDB destination as employee details ————->
Click ok Twice ————->
Go to event handler tab ————->
Drag and Drop Execute SQL Task [on pre validate Event handler for package executable] ————->
Double click on Execute SQL task provide connection if exists ————->
Provide the following SQL command. To clean up the data from destination table ————->
Truncate Table Employee details ————->
Click ok ————->
For an Indepth knowledge on MSBI click on: