Event Handlers
Event-driven programming is support by almost every programming language and also supported by SSIS. An event is the occurrence of an action during the execution life cycle. SSIS package and components (containers, tasks) generate events at various stages of their lifetime. Custom event handlers can be written for these events to extend package functionality and make packages easier to manage at run time. Some examples of events are:
- OnError – generated as the result of an error condition.
- OnPreValidate – fired before validation process starts.
- OnQueryCancel – fired when the user clicks on cancel or during an executable to determine whether it should stop running.
- OnTaskFailed – signals the failure of a task and typically follows OnError event.
- OnPreExecute – indicates that an executable component is about to be launched.
- OnPostExecute – takes place after an executable component finishes running.
While there are several events which are common across all the tasks, there are some events which are specific to a task. For example OnWMIEventWatcherEventOccurred and OnWMIEventWatcherEventTimedout events are associated with the WMIEventWatcher task. To add an event-handler, click the Event Handlers tab in the designer pane, from the left combo-box select the component and from the right combo-box select the event. In the below example, Employee Data Flow Task and its OnPostExecute event have been selected. This Execute SQL Task will do cleanup work after the execution of the Data Flow Task.
Events raised by a component are, by default, processed by its own handler (if it exists) and by a handler for the same event assigned to its parent and grandparent(s) – until the top container is reached. This default behavior can be changed by assigning a FALSE value to the Propagate system variable of a component’s handler (by default, its value is set to TRUE). Doing so will not let the event progress to parent level and no parent’s event handler for that particular event will be called. Propagate system variable is an exceptional system variable which allows user to change its value. Dts.Variables(“Propagate”).Value = False Every component has a property called DisableEventHandlers, by default its value is FALSE. Changing it to TRUE will prevent any of its handlers being triggered in the case of an associated event; however notification of the event is passed up the hierarchy, activating equivalent handlers defined in the parent and grandparent(s) containers. Note:
- Event handlers in SSIS are executed synchronously (as opposed to asynchronous where events are queued up for execution and the main thread may continue executing while events are being queued for processing), the main thread is paused while the event is executed. It’s important to ensure code in the event handlers runs quickly.
- If there are two tasks running in parallel and both raise events at same time, the second event will wait for the event handler to finish processing the first event; in this case the order execution of the event-handler is non-deterministic.
Transaction Support
Databases support transactions at the database level, the SSIS supports transactions at package level. If transaction support is enabled for a package and the package that contains multiple data flow tasks fails, all the database update actions performed by the data flow tasks will be rolled back, thereby ensuring that the database is in a consistent state.
By default, a task initiates its own transactions, commits and rollbacks depending on the result outcome of its execution on a one transaction per task basis. SSIS enables transaction covering more than one task or enabled at different level; while also allows a component to start a new transaction, participate in an existing transaction or not participate in the transaction initiated by parent/grandparent nor start a new transaction.
There are two properties of a SSIS package and its components that control the transaction behavior as follows:
TransactionOption – This property controls the transaction support behavior for the package and components and accepts either of the below three values:
Supported – the default setting, causes a component (task or container) to join a transaction that has been initiated by its parent. If there is no parent transaction, a new transaction is not started.
Required – indicates that the package/component start a transaction unless one is already started by a parent/grandparent. If a transaction already exists, the container joins the transaction by its parent.
NotSupported – indicates that the component does not start a transaction nor joins an existing transaction. Forces a component to execute without transactional support, even if a transaction has been started by its parent. Note that setting this value on a parent container (including the package) does not prevent its children (tasks or containers) from launching their own transactions.
Isolation Level – The isolation level of transaction tells how much two or more transactions running concurrently are isolated to each other. It accepts these values:
Serializable – will lock out any insertions that would match the range, until the other transaction is committed. This is the default and the most restrictive isolation level, ensuring complete independence of the current transaction (highest locking level). Accomplished by placing an update lock on the data to which changes are applied, effectively blocking all other transactions from accessing it as well as preventing reads against data modified, but not yet committed, by them.
ReadUncommitted – the lowest and least restrictive isolation level. Allows viewing uncommitted changes caused by other processes (running within their own transactions) executing in parallel with the current one.
ReadCommitted – level where changes applied by other processes (executing within their own transactions) in parallel with the current are not visible until these transactions commit or rollback.
RepeatableRead – holds locks on data until the transaction completes. Locks are placed on all data that is used in a query, preventing other users from updating the data.
Chaos – behaves like ReadUncommitted, but checks the isolation level of other pending transactions during a write operation so that transactions with more restrictive isolation levels are not overwritten.
Snapshot – new isolation level first featured in SQL Server 2005. Reduces blocking by storing a version of data that one application can read while another is modifying the same data. It indicates that from one transaction, changes made in other transactions can’t be seen, even if it is re-queried. The stored version is maintained in a tempdb database. This may have a negative impact on performance and size of the tempdb database, if modifications are frequent.
The primary benefit of this approach is to provide the ability to run multiple, parallel queries without placing exclusive or sharing locks. Doing so addresses situations where long running transactions interfere with the need for concurrent read access to frequently updated data.
Note:
Simply enabling the transaction support at package level or its component level will not work, unless a windows server has been started viz. Distributed Transaction Coordinator or in short MS DTC. The error shown will be similar to what’s shown below, in case this windows service is not running:
Error: The SSIS Runtime has failed to start the distributed transaction due to error 0x8004D01B “The Transaction Manager is not available.”. The DTC transaction failed to start. This could occur because the MSDTC Service is not running.
Example
In this example, a package with three tasks all use the update statement to update a table, shown in the below images. First the default behaviour will be analysed (TransactionOption = Supported, indicating participate in a transaction if it exists), in the below image below notice that no task is sharing a transaction as a transaction either isn’t available or Sequence Container isn’t initiating a transaction on its own. This means every task inside the Sequence Container has distinct transaction; hence a task2 failure does not affect task1, as task1 is already committed.
Before Package Execution
Package Execution (TransactionOption = Supported)
After Package Execution
Below describes a method of using a transaction. In the scenario, the Sequence Container has been set to initiate a new transaction (by setting TransactionOption = Required at Sequence Container level) if no transaction exists or else participate in existing transaction. Thus all tasks inside the container will share the same transaction initiated by the container. If any of the tasks fail, all the work done by all the tasks will be roll backed as shown below.
Before Package Execution
Package Execution (TransactionOption = Required)
After Package Execution
The next article in this series will explore Checkpoints and Restarting an SSIS Package.