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
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
No comments:
Post a Comment