Events are specific points in time that occur when a map is run (for example, RecordStarted, TransformationEnded, and so on). The events allow users to perform tasks as actions and conditions when an event occurs in the map.

Using events and event actions in Map Editor, you can:

The advantage of event handling is that complex transformations with multiple record types on both source and target can be accomplished. Complex data manipulations, such as record aggregation, unrolling of data, transposing of data, are fairly simple. Event handling allows you flexibility, and customization that you would get from a custom coded solution, without the hassle of building a custom program yourself every time you want to transform data.

This section provides the events and actions that are applicable for DataConnect artifacts.

The following types of events are available:

You can add, edit, and delete events in Simple and Advanced Views:

The following topics are covered in this section:

Each source record event is linked to a target record. You can add, delete, duplicate, and reorder record events. Only the source grid will have record specific events.

The following table provides information about the events and event parameters that can be defined for record, source, and target events.

The following table provides information about the actions and action parameters that can be defined for record, source, and target events.

 

You can only edit the event parameters, associated actions and the action parameters for the defined events. If you want to change the events, then delete the event and add a new event.

You can duplicate single or multiple source events to other targets in Advanced View. You cannot do it in Simple View.

You can reorder events only in Advanced View and not in Simple View.

This section provides the events and actions that are applicable when the v9 compatibility mode is enabled.

Some common scenarios using events include the following:

This section provides the following information.

The event handlers are categorized into the following types:

Map events are events related to the complete transformation. These events occur at certain defined points in a transformation, including before, after, upon aborting, and upon errors. Event handlers are paired with event actions. For instance, you can set a ChangeSource action in the BeforeTransformation event handler. Map events are also called transformation events. The map event handlers are available in the Map file > Configuration tab.The following table provides the map event handlers.

Source events are moments in the transformation timeline that occurs when source data is read and before the target data is transformed and written.

In about 80 percent of transformation designs, source events are used, while target events are used in the remaining 20 percent. This is because data manipulation generally occurs on the source side.The source event handlers are available in map file > Mapping tab > Advanced View > Source tree.The following table provides the source event handlers and the descriptions.

Target events are moments in the transformation timeline that occur during the transformation and when data is written to the target.

The target event handlers are available in map file > Mapping tab > Advanced View > Target tree.

The following table provides the target event handlers and the descriptions.

The BeforeUpsertRecord and AfterUpsertRecord actions are actually combination actions. They are equivalent to performing an Update Record, followed by a conditional Insert Record. It is conditional because the Insert is done only when the Update does not match any records.

Sometimes using UpsertRecord action may be more efficient than using the OnMismatch Error to trap key mismatch errors from the Update Record, and then calling Insert Record from the error handler.

Some actions are specific to certain connectors. For more information, see the following table.

Source or target events that relate to a specific record type are called record type event handlers. This helps distinguish them from other event handlers that have a scope covering the entire transformation. Recognition rules for each record type work together with record type event handlers and their event actions. Whenever a record is read from source or written to target, the event handlers and actions are triggered for that particular record type.The record event handlers are available in map file > Mapping tab > Advanced View > Source or Target tree for each record.

Many of the record specific events are the same as for the general source or target events. The difference is that if your source has three record types (for example, R1, R2 and R3) and you want an action to occur after any one of the three records are read, add the action in the AfterEveryRecord source event.

If you want something different to happen when each of the three types are read, add an action to each of the record specific events:

If you want only one thing different for R2 followed by the same action for all three record types, add a specific action to R2 AfterEveryRecord event. Then, add another action under the AfterEveryRecord source event. See Event Precedence.

Every transformation consists of a series of events that run in a particular order. Understanding the flow of event precedence is important so that you can create an effective transformation design with events triggered in the correct order.

Event actions are processed in a particular order dependent on the event. If multiple actions are associated with a single event handler, the actions are executed in the order in which they are defined. Events are triggered based on a precedence scheme.

You can choose to have more than one action occur during a particular event, and you can choose to have the same action occur during more than one event. There are no restrictions on the number or type of actions.

The following table shows the event precedence for all source events in the order in which they fire.

In the OnDataChange events 1-5, if no event 1 is set, event 2 is triggered first, event 3 next, and so on. You can rearrange actions by dragging action names within the grid on the Map All tab.

From any point within the source flow of events, you may direct the flow to a sub-level of either target or error event precedence. Actions such as Map, Put Record, ClearMapPut Record, and MapPut Record transfer the flow to the target event precedence. Any error that occurs, transfers the flow to the error event precedence unless captured at the expression level using the On Error GoTo trapping.

The target event precedence is based on Map and Put subroutines that uses the following logic:

The following table shows the event precedence for all target events in the order in which they are trapped. One important difference from source event precedence is that Record events override General events. For example, if both General and Record BeforeMap events are set, BeforeMap triggers only at the Record level.

The following table shows the event precedence for all error events in the order in which they are triggered.

Once Map Designer finishes processing the events within the target sub-level, it automatically returns to the basic transformation event precedence at the point it left. The transformation continues through that precedence until sent to another sub-level again, or until the transformation ends. However, once Map Designer finishes processing events within the error event precedence, it must have a Resume action to return to the transformation event precedence. If Map Designer does not encounter a Resume action, the transformation stops and no further records are processed.

Event actions are actions that you specify to occur at a specific point in the transformation. The actions are executed based on the associated events. For instance, if you add a TraceOn action in the BeforeTransformation event handler, the TraceOn action is triggered before the transformation is run.

This section includes the following:

The following event action list is for single-mode connectors. For multi-mode connector actions, see Multimode Event Actions.

The Abort event action causes the transformation to abort. If you want, it can also write a message in the log.

If the transformation is transactional, the transaction is rolled back. If there is an action associated with the AfterTransformation event, it is not executed.

 

The Assert action is similar to the Execute action, with one exception. If the result of an expression is a False value, an OnAssertionError event is triggered.

Assertions allow more control over the exception handling. Using the Reject function gives you control over writing records, but does not allow control of events.

The ChangeSource event action changes your source connection. It allows you to change source files (or tables) during a transformation.

 

If the string starts with a plus sign (+), the contents of the connect string are used to update the current connection settings instead of doing a complete replacement. If the previous connection string was used to define a connection to table "tutor1", you can switch to "tutor2" with the connect string: +Table=tutor2

Choose an event handler, such as BeforeTransformation, and select the ChangeSource action.Type the following connection string: "+File=C:\MyData\testfile.asc"

If the path uses spaces, three options are recommended, in descending order as a best practice:

To create a different source file for each run of a given key in the source file, select OnDataChange event handler., and select ChangeSource action. Type the connection string as:

This uses the current contents of MyVariable concatenated with ".asc" to build a new file name. Since the connection string was prefixed with a plus (+) sign, all of the other connection settings come from the previous connection (if there was one), or it has the connector defaults.

To make this action conditional, pass an empty connection string and it does not trigger.

The ChangeTarget event action changes the target connection information in transformation processing. It allows you to change target files (or tables) during a transformation. The ChangeTarget action is suited for splitting output based on logical data control breaks using the OnDataChange event handler.

 

If the string starts with a plus (+) sign, the contents of the connect string are used to update the current connection settings instead of doing a complete replacement. If the previous connection string was used to define a connection to table "tutor1", you can switch to "tutor2" with the connect string "+Table=tutor2".

ChangeTarget Connection String:

If the path uses spaces, three options are recommended, in descending order as a best practice:

To create a different target file for each run of a given key in the target file, add the following to the OnDataChange event handler:

ChangeTarget Connection String:

This uses the current contents of MyVariable concatenated with ".asc" to build a new file name. Since the connection string is prefixed with a plus (+) sign, all of the other connection settings come from a previous connection, or have the connector defaults.

This example examines representing hexadecimal values in a ChangeTarget connection string. You may want to reference connector properties in a ChangeTarget connection string, and sometimes hexadecimal representations are a requirement of the connector property. For instance, an example is the EDI SegmentTerminator property. Hexadecimal values for properties must be quoted with double quotation marks. In addition, since the string passed to ChangeTarget is an expression, the quoted part needs to be double-double quoted, as in the following example:

The following is equivalent to the previous example:

The Clear event action clears all data from the specified source or target record that is stored in memory. The data is held from all fields in previous records in a memory buffer, until it is either replaced by new data from a new input record or it is cleared by this action.

Source records (not only target records) can now be cleared. This can be useful when there are optional elements of a parent and child relationship, and you want to discard the contents of child records from the previous parent.

 

The Clear Tree event action is similar to the Clear action, with one exception. This action clears the contents of the specified record, and then clears the tree of child records. Clear Tree is used for hierarchical records. For flat files, use the Clear action.

 

This action clears the buffer of target records and initializes the non-null field values. The ClearInitialize action is similar to the Clear action, however, Clear initializes all fields in the specified record layout to null values, while ClearInitialize initializes all values to zero. By default, the action initializes numeric and text fields with a zero value.

 

In the ClearMap event action, when a new record is read, the data stored in the target memory buffer from the previous record is cleared, executes new data from target field expressions and maps the results of the expressions to the target buffer. This combines the functionality of the Clear action and the Map action. A Put Record action or Insert Record action can be used separately after this action.

The ClearMapPutRecord action is the default operation done automatically by Map Designer when a transformation is run without any events being specified.

These actions are combined into one action called ClearMapPutRecord action, so that you do not have to set each one separately. However, it is also possible to break the process down into separate or double actions. See Clear Action, Map Action, Put Record Action, ClearMap Action, and MapPut Record Action.

You can use this action after the Drop Table Action and Create Table Action when using multi-mode connectors.

 

The Execute event action executes an expression that you specify. Execute can also be used to call external .dll files and ActiveX components.

The LogMessage event action writes a message to the error and event log file.

 

The LogTargetRecord action writes current target record field values into the error and event log file. It is useful for diagnostic purposes.

 

The Map event action executes target field expressions and maps the resulting data from the expressions to the target buffer. Here you can initialize variables needed for map expressions.

The MapPut Record event action executes target field expressions, assigns the resulting data to the target buffer, takes data from the target buffer, and writes to the file or table.

The Put Record event action takes data from the target buffer and writes the data to the file or table.

Type the variable used to store the current repetition count. Make sure that the variable is declared Private or Public (see the note in Count).

To manually set the target buffer, add a Put record action with an Execute expression:

To reference the target buffer, add Targets(0) at the front of a default expression.

The Put Tree event action is a recursive tree-walking variant of the Put Record action, writing the buffered records to target. In Put Tree, boolean expressions (Put conditions) are used to control whether or not a particular record is included as part of the general tree operation. See Put Conditions.

When using a Put Tree action for a hierarchical record layout (such as XML), you must specify the parent in the hierarchy, and then children are written in their appropriate order.

When you use the Put Tree Action for a multi-record file with an implied hierarchical relationship, such as ASCII (Fixed), you must plan your map carefully. Map Designer does not define the "parent/child" relationship, so it cannot interpret the relationships between the parent and child tree buffers to write them in a specific order. This logic must be handled manually.

A Put Tree action must be followed by a Clear Tree action to avoid writing duplicate entries. Specify the ClearTree Action into an AfterPutTree event handler.

When using a Put Tree action for a reject record, follow the Put Tree action with a Clear Tree action for the same reject record in the same event.

When working with a hierarchical Unit Of Work (UOW) using the PutTree action, it is sometimes necessary to discard a branch of the UOW or the complete UOW based on the successful transformation of the data within that branch or UOW. Use the put condition in the Target Schema Rules node to control this logic. The put condition must be specified at the upper level of the hierarchy for which the exclusion applies. For example, you have an XML file with the following hierarchy:

To omit vehicles built before 1995, set up your put condition on the Vehicle record using the following expression:

Option EvaluateManualTargets(0).Records("Vehicle").Fields("Year") >= "1995"

To omit all vehicles if any of the vehicles belonging to an owner were built before 1995, enter this expression in the put condition for the Vehicles record. Finally, to omit any owners with vehicles built before 1995, you must enter the expression in the put condition for the Owner record.

Put conditions are conditional expressions used to control the behavior of the Put Tree action. Using the buffered put capability of the engine, a map processes a record and stores the result in a temporary tree of data with other related records. As the Put Tree action scans the buffered data, it writes data to the target or skips it based on the put conditions.

Put conditions are useful when you are working with a group of similar records or with parent and child records, and you do not want to write the data until you have a complete set or until you have found out more about the data.

For example, you can set a put condition on a record type in the target schema so that those records are written only if a particular other source record type is found.

The Query Statistic event action returns a user-specified run-time statistic to a variable. Once populated by QueryStatistic, the related variable may then be used, for example, to populate a message box, message object, or a report file.Use the QueryStatistic Event action to:

QueryStatistic returns a value to a variable. The variable is also declared in the Event Handler dialog box. The value returned in each QueryStatistic action is one of the same run-time statistics recorded in the log file. One advantage of this action is that values are accessible within the transformation at run-time. Using QueryStatistic, these values may be obtained without parsing the log file.

 

 

The Resume event action causes the transformation to continue normally after an error is detected.

There are no parameters or Resume action.

 

The Terminate event action ends the transformation. If the transformation is transactional, it does not roll back, but ends normally. If there is an action associated with the AfterTransformation event, it is executed.

 

The TraceOff event action turns off error tracing and returns the logging to its normal state. This is for single-mode connectors only.

There are no parameters to be specified for the TraceOff action.

 

If you want to troubleshoot a transformation that is failing and not producing detailed log messages, follow these steps.

The TraceOn event action turns on error tracing so that details about each record transformation are written in the error and event log file. Use this action for single-mode connectors only.

There are no parameters to be specified for the TraceOn action.

The Validate Record event action executes the validation rule for the specified source or target record. This action raises an OnRecordValidationError even if the validation rule returns false.

Validation rules are boolean expressions that can be associated with any source or target record.

The Validate Record action can be used to build filters. To do this, write an expression that tests the record on your criteria, and returns a boolean value. True passes validation, while False fails validation and triggers the OnRecordValidationError.

Use the OnRecordValidationError action to set up error handling.

 

When you use a multi-mode connector, a single data stream can be sent from multiple tables to a target database. You can perform multiple operations in one transformation, such as table drops, inserts, and updates directly on the target database.

Multiple output is the only output option for multi-mode connectors. This means that if you need to update, append, or delete records with a multi-mode connector, you must use the multi-mode event actions.

This section provides information for the following multi-mode event actions:

Event actions are actions that you specify to take place at a specific moment in the transformation. Which event they are associated with determines when they are executed. Multimode event actions can be used with multimode connectors only.

This action can be used by multi-mode connectors only. The ClearMapInsert Record event action clears the target record buffer, executes any map expressions, and inserts records into the target table.

The Create Index action can be used only by multi-mode connectors. It creates new indexes with attributes specified in action parameters. The Create Index action is often used as a BeforeTransformation event.

The Create Table action can only be used by multi-mode connectors. It creates new tables with attributes specified in action parameters. The Create Table event action is often used in BeforeTransformation events. It can be placed after a Drop Table action event in a transformation designed to be run multiple times. In this scenario, the existing target tables with old data are dropped, then tables are created for new data

The Delete Record event action may only be used by multi-mode connectors. It deletes records from multiple tables specified in the action parameters. You may write to multiple tables by using this action in a BeforeTransformation event.

The Drop Table event action can be used by multi-mode connectors only. It drops tables specified in the action parameters. This action is often used as a BeforeTransformation event. It can be placed before a CreateTable Action event in a transformation designed to be run numerous times. In this scenario, existing target tables with old data are dropped, then tables are created for new data.

This action executes an expression that you specify. Execute can also be used to call external .dll files and ActiveX components.

The Insert Record event action may only be used by multi-mode connectors. It acts as a query statement so that records can be inserted into specified tables. You may write to multiple tables by using this action as a BeforeTransformation event.

The MapInsert event action may only be used by multi-mode connectors. MapInsert Record executes target field expressions, assigns the resulting data to the target buffer, and inserts records into the target tables.

The SQL File event action may only be used by multimode connectors. It calls specified SQL files to be used in a transformation.

You can use the SQL Statement event action only for multi-mode connectors. It includes code that contains one or more SQL statements. These statements are written to trigger events within the transformation, such as Insert Record action or Delete Record action. SQL commands, such as Truncate or Create Procedure, can be called in SQL Statement action code. This action does not return a value.

You may also embed EZscript expressions within your SQL statements using DJX. The syntax for one-line statements is:

For multiple lines or block statements, the syntax is:

 

The Update Record event action can be used by multimode connectors only. It updates records in multiple tables specified in the action parameters. You may write to multiple tables by using this action as a BeforeTransformation event.Note

If single-mode connectors are used in a transformation and this action is selected, it is ignored in the transformation process. This action is for multimode connectors only, such as SQL Script, or Oracle 8.x Multimode.

If you set the action key on a primary field, note that updates are made to target records that have the following:

The Upsert Record event action combines two actions into one action. It is equivalent to doing an Update Record, followed by a conditional Insert Record. It is conditional because the Insert is done only when the Update does not match any records.

There are cases where using Upsert Record actions may be more efficient than using the OnMismatch Error to trap key mismatch errors from the Update Record and then calling Insert Record from the error handler.

In Advanced View, you can add, edit, and delete events for the following nodes in both Source and Target tree view:

You can manage both record events and source events when you are in Source > All Events view.

Similarly, you can manage record events and target events when you are in Target > All Events view.