Ads 468x60px

Wednesday, September 14, 2011

Learn SSIS : Event handlers, variables, expressions in SSIS - Part 1




SSIS is not just about control flows and dataflows, there are other options available in SSIS which extends functionality of SSIS package, and provides a great flexibility in desiging a successfull ETL solution. Lets take a deep insight of what are those options and how we can take benefit of them.

There are four options which extends functionality of SSIS packages.

1. Event Handlers
2. Variables
3. Expressions
4. SQL Queries

In this post i will be covering Event handlers, and in later series i will cover Variables, Expressions and SQL queries

Event Handlers:

Like any programming languages (C# or JAVA) SSIS also provides Event handlers, which provides functionality of performing some task on a specific event during runtime. At runtime of any SSIS task, series of event takes place, and SSIS Desginer has provided with one tab (Event Handler) to program any action that can be performed on occurance of that event.

Event handlers are created in the same manner as control flow but in different tab as shown.




















How we create events?

Its as simple as creating control flow of package.

1. Select the executable container to which the handler will be assigned.
2. Select the event to which you wish the event handler to react to.
3. Drag control flow container and task and connect them together with precedence constraints.

How Events are actually gets handled in SSIS?

Events can be handled at task or container or package level, following is the order it will be handled.

1. Task
2. Container
3. Package

For example:  If an event is triggered  at the task level and no event handler is defined, the event is passed to container level, if no event handler is defined at container level then it will be passed to package level.

How many events are available?

There are 12 different events that can be handled in SSIS package. Below is the list..

OnError The OnError event is caused by the occurrence of an error.

OnExecStatusChanged The OnExecStatusChanged event occurs when an executable changes its status.

OnInformation When an executable reports information during validation or execution, the OnInformation event occurs.

OnPostExecute OnPostExecute is an event that occurs immediately after an executable has completed running.

OnPostValidate
The OnPostValidate event occurs immediately after an executable has finished its validation.

OnPreExecute The OnPreExecute event occurs immediately before an executable runs.

OnPreValidate The OnPreValidate event occurs immediately before an executable begins its validation.

OnProgress
OnProgress is an event that occurs when an executable makes measurable progress.


OnQueryCancel The OnQueryCancel event occurs automatically at regular intervals to determine if the package should continue running.

OnTaskFailed When a task has failed execution, the OnTaskFailed event occurs.

OnVariableValueChanged
The OnVariableValueChanged event occurs when a variable's value is changed.


OnWarning The OnWarning event is raised when a warning occurs.

Event handlers have a number of properties that allow you to

• assign a name and description to the event handler
• enable or disable the event handler
• determine whether the package fails if the event handler fails
• determine the number of errors that can occur before the event handler fails
• override the execution result that would normally be returned at runtime
• determine the transaction isolation level for operations performed by the event handler, and
• determine the logging mode used by the event handler

Thats all.. I hope this peice of information was usefull.

Thanks..

2 comments:

  1. Hi Kutbuddin Bori

    I faced some problems to understood this Event Handlers concept in ssis, After your clear explanation, i got clear view how to use of the Event Handlers. this is very very good explanation.. i loved it to learn.. Thanks for sharing your knowledge... and please provide remaining one also, like variables, expressions, SQL Queries, i am eagerly waiting for your post....
    once again thanks thanks thanks

    ReplyDelete
    Replies
    1. Hi Naveen..

      thanks for your feedback...

      Here is link for my next article in the series

      http://www.aboutsql.in/2013/01/learn-ssis-expressions-in-ssis-part-2.html

      By the way I have moved my blog to my new URL, so please check for some more interesting articles on SQL SERVER and SSIS @ www.aboutsql.in

      I hope you will like it ...

      Don't forget to leave your valuable comments if you like the post :)

      Thanks,
      Kutbuddin Bori

      Delete