User Guide : Designing and Executing Map : Mapping Target from Source Data : Managing Events and Actions
 
Share this page             
Managing Events and Actions
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:
Receive notifications from the engine that certain things have occurred.
Pre-process or post-process the records.
Perform memory management tasks, such as initializing memory and cleaning up memory.
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.
Rules for Adding Events Using Map Wizard
The following are the rules for adding events using Map Wizard.
Condition
Details
Source is single record type and Target is single record type
Add a single source record to target record RecordStarted event, OutputRecord action.
Source is single record, Target is multi-record or hierarchical with > 1 record type
Add SourceRecord event to each target record (can add for fivetargets).
If target is hierarchical with a single root record, then the first event is SourceStarted, OutputRecord action.
For records 2-X where X is not > 5 of the target, add a RecordStarted event, OuputRecord action. This may not be exactly what you need but will give an idea of what is required. You can later edit the events in the Map Editor. This scenario will likely be accurate for new hierarchical files that is based on the source schema or hierarchical files that have a single root record and few child records.
Source is multi-record or hierarchical with > 1 record type and target has single record
Add a source record to the single record RecordStarted event, OutputRecord action for each source record. This may not be exactly what you need but will give an idea of what is required. You can later edit the events in the Map Editor.
Both source and target are multi-record or hierarchical.
Add up to three source record to each of the first three RecordStarted events, OutputRecord events. This may not be exactly what you need but will give an idea of what is required. You can later edit the events in the Map Editor.
For Joins, both the source and the join must be single record. The target must be single record.
Add one RecordStarted, Output record event/action from source to join and one RecordStarted, Output record event/action from join to target.
Events and Actions
This section provides the events and actions that are applicable for DataConnect artifacts.
The following types of events are available:
Map events: These are events and actions for the overall map. For more information, see Configuring Map Events.
Record Events: Events occur during processing of source records.
Source Events: Set conditions and actions for specific source.
Target Events: Set conditions and actions for specific target
You can add, edit, and delete events in Simple and Advanced Views:
In Simple View > Mapping tab, click Events to add events. To view the fields, click Fields.
In Advanced View > Mapping tab, click the required source or target record for which you want to add the events.
The following topics are covered in this section:
Adding Record Event
Adding Source Event
Adding Target Event
Events and Event Parameters
Actions and Action Parameters
Editing Event Records
Duplicating Events to Other Targets
Reordering Events in Source and Target Grids
Deleting Events in Source and Target Grids
Adding Record Event
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.
To add record events for a source record:
1. Do any of the following:
In Simple View, click Events and in the source grid, click and select Record Event.
In Advanced View > Source tree view, click Events under the source record for which you want to add an event and click /download/attachments/24975466/Map_Mapping_AddField_Icon.png?version=1&modificationDate=1487964384147&api=v2.
The Add Record Events wizard is displayed.
You can also add record events for source records in the All Events view. To do this, in the source tree view, click All Events, click the arrow next to /download/attachments/24975466/Map_Mapping_AddField_Icon.png?version=1&modificationDate=1487964384147&api=v2 icon and select Record Event.
2. Select the target for which you want to add an event and click Next.
A window is displayed where you can select an event and define event parameters, condition expressions, true and false actions, and action parameters.
3. In the Events section, click and select the required event. In the Event Parameters section, specify the values for the parameters. For information about the events and event parameters, see Events and Event Parameters.
You can do the following:
Click /download/attachments/24975553/Map_Source_UpArrow_Icon.png?version=1&modificationDate=1487965242692&api=v2 to move the event up or click /download/attachments/24975553/Map_Source_DownArrow_Icon.png?version=1&modificationDate=1487965242755&api=v2 to move the event down.
Note:  The order of events is important because they will be executed in the order they are listed.
To change the event, click within the cell and from the drop-down list, select another event.
Click /download/attachments/24975553/Map_Source_Delete_Icon.png?version=1&modificationDate=1490840984952&api=v2 to delete an event.
Click /download/attachments/24975553/Map_Source_Copy_Icon.png?version=1&modificationDate=1490841023672&api=v2 to copy an event.
4. On the True Actions tab, in the Actions section, click and select the required action. In the Action Parameters section, specify the values for the action parameters. For information about the actions and action parameters, see Actions and Action Parameters.
You can do the following:
Click /download/attachments/24975553/Map_Source_UpArrow_Icon.png?version=1&modificationDate=1487965242692&api=v2 to move the action up or click /download/attachments/24975553/Map_Source_DownArrow_Icon.png?version=1&modificationDate=1487965242755&api=v2 to move the action down.
Note:  The order of action is important because they will be executed in the order they are listed.
To change the action, click within the cell and from the drop-down list, select another action.
Click /download/attachments/24975553/Map_Source_Delete_Icon.png?version=1&modificationDate=1490840984952&api=v2 to delete an action.
Click /download/attachments/24975553/Map_Source_Copy_Icon.png?version=1&modificationDate=1490841023672&api=v2 to copy an action.
5. In Condition Expression section, click and specify an expression in the EZscript editor.
6. On the False Actions tab, specify the Actions and Action Parameters. The available actions and the action parameters are same as True Actions.
Note:  If a conditional expression is added, you must specify the false action.
7. Click Finish.
The event is added to the Events view in the source grid.
Adding Source Event
To add source event:
1. Do any of the following:
In Simple View, click Events. In the source grid, click and select Source Event.
In Advanced View > Source tree view, click Source Events under the source record for which you want to add an event and click /download/attachments/24975466/Map_Mapping_AddField_Icon.png?version=1&modificationDate=1487964384147&api=v2.
The Add Source Event wizard is displayed.
You can also add source events in the All Events view. To do this, in the source tree view, click All Events, click the arrow next to /download/attachments/24975466/Map_Mapping_AddField_Icon.png?version=1&modificationDate=1487964384147&api=v2 icon and select Source Event.
The window to define events, actions, and conditions is displayed.
2. In the Events section, click and select the required event. In the Event Parameters section, specify the values for the parameters. For information about the events and event parameters, see Events and Event Parameters.
You can do the following:
Click /download/attachments/24975553/Map_Source_UpArrow_Icon.png?version=1&modificationDate=1487965242692&api=v2 to move the event up or click /download/attachments/24975553/Map_Source_DownArrow_Icon.png?version=1&modificationDate=1487965242755&api=v2 to move the event down.
Note:  The order of events is important because they will be executed in the order they are listed.
To change the event, click within the cell and from the drop-down list, select another event.
Click /download/attachments/24975553/Map_Source_Delete_Icon.png?version=1&modificationDate=1490840984952&api=v2 to delete an event.
Click /download/attachments/24975553/Map_Source_Copy_Icon.png?version=1&modificationDate=1490841023672&api=v2 to copy an event.
3. On the True Actions tab, in the Actions section, click and select the required action. In the Action Parameters section, specify the values for the action parameters.For information about the actions and action parameters, see Actions and Action Parameters.
You can do the following:
Click /download/attachments/24975553/Map_Source_UpArrow_Icon.png?version=1&modificationDate=1487965242692&api=v2 to move the action up or click /download/attachments/24975553/Map_Source_DownArrow_Icon.png?version=1&modificationDate=1487965242755&api=v2 to move the action down.
Note:  The order of action is important because they will be executed in the order they are listed.
To change the action, click within the cell and from the drop-down list, select another action.
Click /download/attachments/24975553/Map_Source_Delete_Icon.png?version=1&modificationDate=1490840984952&api=v2 to delete an action.
Click /download/attachments/24975553/Map_Source_Copy_Icon.png?version=1&modificationDate=1490841023672&api=v2 to copy an action.
4. In Condition Expression section, click and specify an expression.
5. On the False Actions tab, specify the Actions and Action Parameters. The available actions and the action parameters are same as True Actions.
Note:  If a conditional expression is added, you must specify the false action.
6. Click Finish.
The event is added to the Source Events view in the source grid.
Adding Target Event
To add target event:
1. Do any of the following:
In Simple View, click Events. In the target grid, click /download/attachments/24975466/Map_Mapping_AddField_Icon.png?version=1&modificationDate=1487964384147&api=v2.
In Advanced View > Target tree view, click Target Events under the target record for which you want to add an event and click /download/attachments/24975466/Map_Mapping_AddField_Icon.png?version=1&modificationDate=1487964384147&api=v2.
The Add Target Event wizard is displayed.
You can also add target events in the All Events view. To do this, in the target tree view, click All Events, click the arrow next to /download/attachments/24975466/Map_Mapping_AddField_Icon.png?version=1&modificationDate=1487964384147&api=v2 icon and select Target Event.
The window to define events, actions, and conditions is displayed.
2. In the Events section, click and select the required event. In the Event Parameters section, specify the values for the parameters. For information about the events and event parameters, see Events and Event Parameters.
You can do the following:
Click /download/attachments/24975553/Map_Source_UpArrow_Icon.png?version=1&modificationDate=1487965242692&api=v2 to move the event up or click /download/attachments/24975553/Map_Source_DownArrow_Icon.png?version=1&modificationDate=1487965242755&api=v2 to move the event down.
Note:  The order of events is important because they will be executed in the order they are listed.
To change the event, click within the cell and from the drop-down list, select another event.
Click /download/attachments/24975553/Map_Source_Delete_Icon.png?version=1&modificationDate=1490840984952&api=v2 to delete an event.
Click /download/attachments/24975553/Map_Source_Copy_Icon.png?version=1&modificationDate=1490841023672&api=v2 to copy an event.
3. On the True Actions tab, in the Actions section, click and select the required action. In the Action Parameters section, specify the values for the action parameters.For information about the actions and action parameters, see Actions and Action Parameters.
You can do the following:
Click /download/attachments/24975553/Map_Source_UpArrow_Icon.png?version=1&modificationDate=1487965242692&api=v2 to move the action up or click /download/attachments/24975553/Map_Source_DownArrow_Icon.png?version=1&modificationDate=1487965242755&api=v2 to move the action down.
Note:  The order of action is important because they will be executed in the order they are listed.
To change the action, click within the cell and from the drop-down list, select another action.
Click /download/attachments/24975553/Map_Source_Delete_Icon.png?version=1&modificationDate=1490840984952&api=v2 to delete an action.
Click /download/attachments/24975553/Map_Source_Copy_Icon.png?version=1&modificationDate=1490841023672&api=v2 to copy an action.
4. In Condition Expression section, click and specify an expression.
5. On the False Actions tab, specify the Actions and Action Parameters. The available actions and the action parameters are same as True Actions.
Note:  If a conditional expression is added, you must specify the false action.
6. Click Finish.
The event is added to the Target Events view in the target grid.
Events and Event Parameters
The following table provides information about the events and event parameters that can be defined for record, source, and target events.
Event Type
Event Name
Event Description
Event Parameter
Event Parameter Description
Record Event
ErrorFound
Triggered when an error occurs during map execution.
-
-
Record Event
GroupEnded
Triggered when the end of a group of records is detected in the input and before the next group starts.
group type
Determines the group boundary. The values are:
field list: The group boundary is determined by watching values for the specified list of fields from the source record.
expression: The group boundary is determined by watching the value computed by an expression. This event also occurs when the input stream ends.
group key
Based on the selected group type, specify the group key:
If group type is field list, then click to open the Create Field List window. Select the Source Fields and click to move it to the Field List box. Click OK. The value appears in the Value column.
If group type is expression, then click to open the EZscript Experssion editor and specify an expression.
Record Event
GroupStarted
Triggered when the start of a group of records is detected in the input.
group type
Determines the group boundary. The values are:
field list: The group boundary is determined by watching values for the specified list of fields from the source record.
expression: The group boundary is determined by watching the value computed by an expression. This event also occurs when the input stream ends.
group key
Based on the selected group type, specify the group key:
If group type is field list, then click to open the Create Field List window. Select the Source Fields and click to move it to the Field List box. Click OK. The value appears in the Value column.
If group type is expression, then click to open the EZscript Experssion editor and specify an expression.
Record Event
Source Event
RecordEnded
Triggered after processing a record is completed, before reading the next record. This event is useful for working with grouped sets of records where there are no distinct boundaries between the different groups.
Note:  Field values for the new record are not visible to mapping expressions when this event occurs.
-
-
Record Event
RecordRejected
Triggered when a source record rejected target.
reason
Specify the reason for rejecting the record.
Record Event
Source Event
RecordStarted
Triggered after reading each instance of the source record.
-
-
Record Event
Source Event
SourceStarted
Triggered before records are read in the input.
-
-
Record Event
Source Event
SourceEnded
Triggered when processing a source has been completed.
-
-
Record Event
SubTreeStarted
Triggered when the start of a new instance of a sub-tree is detected in the input.
-
-
Record Event
SubTreeEnded
Triggered after all the records representing a complete subtree of a hierarchical structure are read from the input stream. This event occurs before the source transitions to the parent or to a sibling of the current tree. This event also occurs when the input stream ends.
-
-
Record Event
UserDefinedEventDetected
Triggered when a user-defined event is raised.
user-defined event name
Specify a name for the user-defined event.
You can define and raise user-defined events to monitor events other than the pre-defined events. You can use the RaiseUserDefinedEvent event action and the UserDefinedEventDetected event. In a map, you can define the RaiseUserDefinedEvent event action on the False Action tab and specify the event name and the parameter string. The UserDefinedEventDetected event collects the User Defined Event based on the event name. When the map is run, the parameter string defined in the UserDefinedEventDetected event is executed.
Target Event
RecordOperationStarted
Triggered just before modifying an output record (insert, delete, update, upsert, blank for any operation).
target record address
If the record address parameter is specified, this event is triggered every time that record is output (operation is ignored). If no record address is specified, then any record whose operation matches the operation parameter will trigger the event.
If neither is specified, then every record will trigger the event.
operation
Select one of the following as the operation that must be performed when the event is triggered:
insert
update
delete
upsert
Target Event
RecordOperation Ended
Triggered after modifying an output record (insert, delete, update, upsert, blank for any operation). If the record address parameter is specified, this event is triggered every time that record is output (operation is ignored). If no record address is specified, then any record whose operation matches the operation parameter will trigger the event. If neither is specified, then every record will trigger the event.
Same as RecordOperationStarted.
Actions and Action Parameters
The following table provides information about the actions and action parameters that can be defined for record, source, and target events.
 
Event Type
Action
Action Description
Action Parameter
Action Parameter Description
Record Event
Abort
Stops the transformation and returns the return code.
reason code
Returns the integer value as the return code.
Record Event
Aggregate
Evaluates the aggregation functions for aggregate fields in the target record.
-
-
Record Event
Break
Breaks the execution process. Execution resumes with the next link on the current source record.
-
-
Record Event
ClearRecord
Clears the data values from the fields for the target record.
target record name
Select the target record.
cascade
Select True to clear child records. Else, select False
Record Event
ClearRecordSet
Deletes all the rows in the intermediate target.
intermediate target name
Select the intermediate target name.
Record Event
Discard
Current input record is rejected.
-
-
Record Event
Source Event
Target Event
ExecuteCommand
Triggers the command that must be executed by the target.
target name
Select the target.
command
Specify the command to be executed.
command type
Indicates how the SQL string or command is processed before it is passed to the connector. Select any of the following command type processor:
plain_text: Uses the string as the SQL command and additional processing of the command is not required.
expression: Command is evaluated as an expression and the return value is passed to the connector as the SQL command.
preprocessor: Command is preprocessed using the SQL statement preprocessor (DJX/DJXBEGIN/DJXEND) and the result is passed to the connector.
Record Event
Source Event
Target Event
ExecuteExpression
Executes the expression.
expression
Click to open the EZscript Experssion editor and specify an expression.
Record Event
OutputRecord
Evaluates the target field expressions for a record and writes it to the target. This action does not evaluate the aggregate fields.
preserve
After the output operation, the target record values are cleared if this parameter is set to false.
record count
Click to open the EZscript Experssion editor and specify an expression.
Record Event
OutputRecordSet
Uses a secondary source or an intermediate target as a source for a downstream target.
source name
Select the source or to be processed.
preserve
After the output operation, the target record values are cleared if this parameter is set to false.
max record count expression
Click to open the EZscript Experssion editor and specify an expression to limit the number of records computed in the output.
Record Event
RaiseUserDefinedEvent
Raise a user-defined event and optionally pass a string containing event parameter data.
event name
Name of the user-defined event.
parameter string
String containing event parameter data.
Record Event
Reject
Signals that the current input record must be rejected.
reason code
Allows you to associate the reject with an integer value that can be mapped to the reason for the reject.
Record Event
Terminate
Terminates the map execution and causes the engine to return a successful return code.
-
-
Editing Event Records
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.
To edit an event record:
1. Go to any of the following views as required:
In Simple View, click Events - Displays source and target grids with the defined events.
In Advanced View:
Source tree > All Events - Displays source and record events defined for a map
Source tree > Events - Displays all events defined for a source record
Source tree > Source Events - Displays source events defined for a specific source
Target tree > All Events - Displays all target events defined for a map
Target tree > Target Events - Displays target events defined for a specific target
2. Click the event record that you want to edit and click .
The window where you can edit the event parameters, actions, and action parameters is displayed.
3. Edit the required details and click Finish. For information about the event parameters and actions, see Events and Event Parameters and Actions and Action Parameters.
Duplicating Events to Other Targets
You can duplicate single or multiple source events to other targets in Advanced View. You cannot do it in Simple View.
To duplicate events:
1. In Advanced View, go to any of the following nodes as required:
Source tree > Events - Displays all events defined for a source record.
Source tree > Source Events - Displays source events defined for a specific source.
2. Click the event record that you want to duplicate and click . To select multiple events, press and hold the Ctrl key.
The Duplicate Event window is displayed.
3. Select one or more targets to which you want to duplicate the selected event.
4. Select any of the following options:
Insert Only: Adds the event to the selected targets.
Insert and Override: Overrides existing events in the selected targets.
5. Click Finish.
The copy of the event is added to the selected targets.
Reordering Events in Source and Target Grids
You can reorder events only in Advanced View and not in Simple View.
To reorder events:
1. In Advanced View, go to any of the following nodes as required:
Source tree > All Events - Displays source and record events defined for a map
Source tree > Events - Displays all events defined for a source record
Source tree > Source Events - Displays source events defined for a specific source
Target tree > All Events - Displays all target events defined for a map
Target tree > Target Events - Displays target events defined for a specific target
2. To move the event up, click /download/attachments/24975553/Map_Source_UpArrow_Icon.png?version=1&modificationDate=1487965242692&api=v2.
3. To move the event down, click /download/attachments/24975553/Map_Source_DownArrow_Icon.png?version=1&modificationDate=1487965242755&api=v2.
Deleting Events in Source and Target Grids
To delete an event:
1. Go to any of the following views as required:
In Simple View, click Events - Displays source and target grids with the defined events.
In Advanced View:
Source tree > All Events - Displays source and record events defined for a map
Source tree > Events - Displays all events defined for a source record
Source tree > Source Events - Displays source events defined for a specific source
Target tree > All Events - Displays all target events defined for a map
Target tree > Target Events - Displays target events defined for a specific target
2. Click the event record that you want to delete and click /download/attachments/24975466/Map_Mapping_DeleteField_Icon.png?version=1&modificationDate=1487964384147&api=v2.
A message asking for confirmation is displayed.
3. Click Yes.
The event is deleted.
Events and Actions for XML Maps (When v9 Compatibility Mode is Enabled)
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:
Perform an update in a source event action. If the update fails, it can trigger an OnMisMatchError event.
Use OnMisMatchError if you are inserting records, and set up rejects for mismatched values.
Include the BeforeFirstRecord and AfterFirstRecord events in a transformation to look for header information, and to check if the source file is correct or not.
Use OnDataChange events to monitor parent and child relationships.
Using an OnEOF event, maintain the total of all records processed, and send the value to a file or log. For instance, if you expect 127 records and only receive 126, read the log file, and determine why the record is not written.
This section provides the following information.
Event Handlers
Event Precedence
Event Actions
Multimode Event Actions
Event Handlers
The event handlers are categorized into the following types:
Map Event Handlers
Source Event Handlers
Target Event Handlers
Record Event Handlers
Map Event Handlers
Map events are events related to the complete transformation. These events occur at certain defined points in a transformation, including before, after, upon stopping, 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.
Event Handler Name
Description
Possible Uses
BeforeTransformation
Triggered after the transformation is initialized and before anything else is done. This is the first event in every transformation.
Note:  When running a transformation in Clear File/Table or Replace File/Table mode, the Target table is cleared and recreated prior to executing the BeforeTransformation event.
Changing source or target connections, setting up dynamic SQL lookups, creating new indexes, creating or dropping tables, inserting or deleting records, and declaring object variables.
AfterTransformation
Triggered after all the records have been processed and before the transformation is terminated. This is the last event in any transformation.
Summing final totals in record aggregation
OnAbort
Triggered when the transformation is stopped.
Logging stop conditions
OnAssertionError
Triggered on the failure of an Assert action. This event handler is specific to either the source or the target. For example, if you set an event on the target side, it does not trigger if an Assert action fails on the source side.
Exception error handling
OnError
Triggered when an error occurs.
Error handling
OnRecordValidationError
Triggered when the Validate Record action fails.
 
Source 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.
Source Event Handler Name
Description
Possible Uses
BeforeFirstRecord
Triggered before reading the first source record.
Executing actions once before any records are read, initialize variables, start Dynamic SQL connections.
AfterFirstRecord
Triggered after reading the first source record.
Executing actions after the first record is read. All records are read in this event, including the first record.
AfterEveryRecord
Triggered as soon as a new record is read and before any processing occurs.
Note:  If there is an error reading the new record, or if the end of file is reached, this event does not occur.
Executing actions once per record read.
BeforeEveryRecord
Triggered before a new record is read and after all the previous records are processed.
Initializing variables, starting Dynamic SQL connections.
OnAssertionError
Triggers when an Assert action statement returns false.
Verifying source field values.
OnEOF
Triggered when the end of the source file is reached.
Footers, totals, final record writing, ChangeSource action.
OnError
Triggered when an error occurs reading a source record.
Error handling, ChangeTarget action.
OnRecordValidationError
Triggers when a validation rule returns false.
Verifying data against a schema.
OnTruncateError
Triggered when a data truncation error is detected.
Handling truncation errors.
Target Event Handlers
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.
Target Event Handler Name
Description
Possible Uses
AfterDeleteRecord
Triggered after a Delete Record action.
 
AfterInsertRecord
Triggered after an Insert Record action. This event is not valid without a ClearMapInsert or MapInsert Record action.
 
AfterMap
Triggered after a Map action.
Clearing map expression variables.
AfterPutRecord
Triggered after a Put Record action.
Logging successful writing of a record.
AfterPutTree
Triggered after a Put Tree action.
 
AfterUpdateRecord
Triggered after an Update Record action.
 
AfterUpsertRecord
Triggered after the Upsert Record action.
 
BeforeDeleteRecord
Triggered before a Delete Record action.
 
BeforeInsertRecord
Triggered before an Insert Record action.
 
BeforeMap
Triggered before a Map action.
Initializing map expression variables needed for Map expressions
BeforePutRecord
Triggered before a Put Record action.
Performing final validation before putting data in a target file or table.
BeforePutTree
Triggered before a Put Tree action.
 
BeforeUpdateRecord
Triggered before an Update Record action.
Updating record, and conditionally inserting based on matching records
BeforeUpsertRecord
Triggered before an Upsert Record action. See also Trapping Key Mismatch Errors.
Updating record, and conditionally inserting based on matching records
OnAbort
Triggered when a transformation is stopped.
Logging stop conditions.
OnAssertionError
Triggered when an Assert action fails. This event handler is specific to the target (for example, if you set an event on the target side, it does not fire if an Assert action fails on the source side).
Exception error handling.
OnDiscard
Triggered when a source record is deleted from the transformation and not written to any target file.
Logging discards or terminating when a discard is done.
OnError
Triggered when any type of error in the target occurs.
Error handling.
OnRecordValidationError
Triggered when the content of a record fails validation.
 
OnReject
Triggered when a record is written to a specified reject file.
Logging record rejects and terminating when a record is rejected.
Trapping Key Mismatch Errors
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.
Name
Description
Supported Connectors
OnConstraintError
Triggered when a constraint error occurs, or an integrity rule is violated.
ODBC and ODBC Multimode
ODBC does not distinguish between key and null value violations. The OnConstraintError event should catch both types of errors and other integrity violations.
OnDuplicateKeyError
Triggered when a duplicate key error occurs. (These errors occur when you try to insert a duplicate key in an indexed target where indexes must be unique.)
Access, Oracle, Oracle Multimode
OnMismatch
Triggered when a mismatch occurs because of an UpdateRecord or DeleteRecord error event. UpdateRecord or DeleteRecord fails when there is no record in the target that matches the key of the record being updated or deleted.
All multimode connectors
OnNullValueError
Triggered when a null value is detected. Null errors occur when a null value is placed into a target field that does not allow null values.
Access, Oracle, Centura SQL Base, SQL Server, Sybase SQL Server, Sybase SQL Anywhere
OnOverflowError
Triggered when a data overflow error occurs. Overflow errors occur when the source data is numeric and contains more digits than the target field's precision.
All multimode connectors
OnTruncateError
Triggered when a data truncation error occurs. Truncation errors occur when the source data is text and contains more characters than the target field's size.
Access, ASCII (Delimited), ASCII (Fixed), BCP, Oracle, Oracle Direct Path, ODBC, SQL Loader
Record Event Handlers
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:
R1 AfterEveryRecord event
R2 AfterEveryRecord event
R3 AfterEveryRecord event
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.
Record Event Handler Name
Description
Possible Uses
AfterFirstRecord
Triggered immediately after the first record of this type is read.
Trigger a ChangeSource or ChangeTarget, Validation
AfterEveryRecord
Triggered as soon as a record of this type is read before it is processed.
Handle multiple record types. This is where actions associated with any particular record type are placed to answer the question, "What should be done with a record of this type?"
BeforeFirstRecord
Triggered before the first record of this type is read.
Trigger a ChangeSource or ChangeTarget, Validation
BeforeEveryRecord
Triggered before the next record of this type is read and after all the previous records of this type is processed.
Handle multiple record types. This is where actions associated with any particular record type are placed to answer the question, "What should be done with a record of this type?"
OnDataChange1
OnDataChange2
OnDataChange3
OnDataChange4
OnDataChange5
Set the action taken when the monitored data changes. OnDataChange1 is the first event to be triggered, OnDataChange2 is the second event, and so on.
Monitor changes in your data. For instance, you can monitor a list of sales accounts by state to determine the states that were buying the most of your product in a given period.
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.
Source Event Precedence
The following table shows the event precedence for all source events in the order in which they fire.
Event Type
Order of Precedence
Event Name
Source
1
BeforeTransformation
Source Record
2
Source Filter Expressions (for each record)
 
3
Source Sort (for each record)
 
4
Source Filter Range (for all records)
 
5
BeforeFirstRecord
 
6
BeforeEveryRecord
Source General
7
BeforeFirstRecord
 
8
BeforeEveryRecord
Source OnDataChange
9
OnAnyDataChange
 
10
OnAllDataChange
 
11
OnDataChange events 1-5
Source Record
12
AfterFirstRecord
 
13
AfterEveryRecord
Source General
14
AfterFirstRecord
 
15
AfterEveryRecord
 
16
On End of File
Source
17
AfterTransformation
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.
Note:  The AfterFirstRecord source event is triggered after OnDataChange events. If you have imported .djs files from previous versions, you may have to change how your events fire so that they are consistent with the current event precedence.
Moving From Source to Target or Error Events
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.
Target Event Precedence
The target event precedence is based on Map and Put subroutines that uses the following logic:
If Map Designer encounters an error within the Map subroutine, it disregards the Put subroutine unless indicated (within the error event precedence) to Put and then Resume.
If indicated to Put without Resume, Map Designer writes data to the target and stops. No further data is processed.
If Map Designer encounters an error within the Put subroutine, it uses error event precedence, which requires a Resume action to return to transformation event precedence.
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.
Event Type
Order of Precedence
Event
Map subroutine
1
Target General BeforeMap
 
2
Target Record BeforeMap
 
3
Map Action
 
4
Target Record AfterMap
 
5
Target General AfterMap
Filter expressions and sampling
6
Target filter expressions
 
7
Target filter sample
Put subroutine
8
Target General BeforePut Record
 
9
Target Record BeforePut Record
 
10
Put action
 
11
Target Record AfterPut Record
 
12
Target General AfterPut Record
Error Event Precedence
The following table shows the event precedence for all error events in the order in which they are triggered.
Event Type
Order of Precedence
Event Name
Expression-level
1
Expression error (for example, On Error Goto statement)
Source or Target record-level
2
Source record error or Target record error
Source or Target General event -level
3
Source General event error or Target General event error
Target record events
4
Target record OnError
Target general events
5
Target General OnError
Transformation-level error events
6
Transformation-level Error
Note:  If Map Designer encounters an error in the error event precedence, the transformation stops. You cannot trap errors within an error event.
Reaching Transformation End
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
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:
Event Actions List
Abort Action
Assert Action
ChangeSource Action
ChangeTarget Action
Clear Action
Clear Tree Action
ClearInitialize Action
ClearMap Action
ClearMapPut Record Action
Execute Action
LogMessage Action
LogTargetRecord Action
Map Action
MapPut Record Action
Put Record Action
Put Tree Action
QueryStatistic Action
Resume Action
Terminate Action
TraceOff Action
TraceOn Action
Validate Record Action
Event Actions List
The following event action list is for single-mode connectors. For multi-mode connector actions, see Multimode Event Actions.
Event Action Name
Description
Abort
Causes the transformation to stop. If the transformation is transactional, the transaction is rolled back. If there is an action associated with the AfterTransformation event, it is not executed.
Assert
Executes an expression that you specify. It is similar to the Execute action, with one exception – if the result of an expression is a False Boolean value, an OnAssertionError event is triggered.
ChangeSource
Changes the source connections in transformation processing. Using this action, several source connections can be made in a single transformation.
ChangeTarget
Changes the target connections in transformation processing. Using this action, several target connections can be made in a single transformation.
Clear
Clears the contents of a target record memory buffer.
ClearInitialize
Clears the contents of a target record buffer and initializes non-null fields with '0' values.
ClearMap
When a new record is read, the data stored in the target memory buffer from the previous record is cleared, and new field data is manipulated and assigned to target fields.
ClearMapPut Record
Combines the functionality of Clear, Map and Put into one action. The target record buffer is cleared, target field expressions are executed, and the resulting data is written to the target file.
Note:  Before you run the transformation, you will not see data in the Results column of the target record layout grid unless you have defined a Put action in source events. The automatic ClearMapPut does not take effect until the transformation is run. To see results for multiple source/target records, you must have rules defined to see results for only the records that are identified, mapped, and written to the target record.
Clear Tree
Similar to the Clear Action, with one exception. It clears the contents of the specified record, walks the tree of child records, and finally clears the record buffer.
Execute
Executes an expression that you specify. It can also be used to call external .dll files and ActiveX components.
LogMessage
Writes a message to the error and event log file.
LogTargetRecord
Writes current target record field values into the error and event log file. This is very useful for diagnostic purposes.
Map
Executes the target field expressions and maps the results of the expressions to the target buffer. The target field expressions usually includes data from the source fields.
MapPut Record
Executes target field expressions, assigns the resulting data to the target buffer and writes the data, but does not clear the buffer. If data from previous records is assigned to fields that do not exist in the new record, that data is written along with the data from the new record.
Note:  Before you run the transformation, the data is not seen in the Results column of the target record layout grid unless you have defined a Put action within your source events. The automatic ClearMapPut does not take effect until the transformation is run. To view the results for multiple source or target records, you must have rules defined, and then you can view the results for only the records that are identified, mapped, and written to the target record.
Put Record
Takes data from target buffer and writes to the file or table.
Note:  Before you run the transformation, the data is not seen in the Results column of the target record layout grid unless you have defined a Put action within your source events. The automatic ClearMapPut does not take effect until the transformation is run. To view the results for multiple source or target records, you must have rules defined, and then you can view the results for only the records that are identified, mapped, and written to the target record.
Put Tree
A recursive tree-walking variant of the Put Record action. In Put Tree, Boolean expressions (Put rules) are used to control whether or not a particular record is included as part of the general tree operation. This action allows you to collect data in memory in the target buffer. Then, yon can decide to write the data to the target or to clear the data. Also, see Put Record Action.
QueryStatistic
Returns a user-specified run-time statistic to a variable. Once populated by this action, the related variable may then be used to populate a message box, message object, or a report file.
Resume
Causes the transformation to continue normally after an error is detected.
Terminate
Ends the transformation without stopping it. 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.
TraceOff
Turns off error tracing and returns logging to its normal state.
TraceOn
Turns on error tracing so that details about each record transformation are written in the error and event log file.
Upsert Record
Combines two actions into one action. It is equivalent to doing an Update Record, followed by a conditional Insert Record, inserted only when Update does not match any records.
Validate Record
This action executes the validation rule for the specified source or target record. Validate Record raises an OnRecordValidationError event if the validation rule returns False.
Abort Action
The Abort event action causes the transformation to stop. 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.
Parameters
Description
Message
Message that is written to the error and event log when the transformation is stopped.
The maximum number of characters are 255.
The message is passed to the Event Actions > Parameters Values column, and appears in brackets in the Parameters column.
Example
 
Situation
Transformation must stop if any source data fields are truncated.
Action
Select the OnTruncateError target event.Select the Abort action.In the Message parameter, type "Data was truncated."
Result
If a text field is read that has too many characters for the target field, a message is written to the error and event log that says "Data was truncated." and the transformation is stopped.
Assert Action
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.
Parameters
Description
Expression
Select False to trigger an OnAssertionError event.
ChangeSource Action
The ChangeSource event action changes your source connection. It allows you to change source files (or tables) during a transformation.
Caution!  Actions set in an event handler after the ChangeSource action are ignored. If you have actions that must be triggered by the same event handler, you must set these first.
 
Parameters
Description
Source Name
Select Source. This is the normal source file.
Connection String
Type the connection string to the source file.
Example: The following connection string specifies a connection to the database "test.mdb" and to the table "tutor1".
Database=test.mdb;UserID=Admin;Table=tutor1;WhereStmt=SystemTables=False; Views=True;CodePage=ANSI;Password=;SystemDB=system.mdw
Plus Sign Usage in String Syntax
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
Example 1
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:
Use a variable based on a macro for the path string.
Map to a network share name without spaces.
Enclose the path in single quotation marks: "+File='C:\My Data\testfile.asc'"
Example 2
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:
"+File=" & MyVariable & ".asc"
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.
Note:  If you run a ChangeSource action for OnEOFevent, it jumps into an infinite loop. To avoid this issue, place a flow control in EZscript Expressions editor.
ChangeTarget Action
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.
Caution!  Actions specified in an event handler after the ChangeSource action are ignored. If you have actions that must be fired by the same event handler, specify these first.
 
Parameters
Description
Target Name
Select Target or Reject. Target is the default value.
Connection String
Specify the script for building the connection string.
Example: The following connection string sets a connection to the test.mdb database and to tutor1 table.
Database=test.mdb;UserID=Admin;Table=tutor1;WhereStmt=SystemTables=False;Views=True;CodePage=ANSI;Password=;SystemDB=system.mdw
Append
Appends output to an existing target file (or table) with the specified name. The default is value is False.
You can also use an EZscript expression if you want to change the mode dynamically. The script must evaluate as false (or 0) if the file has to be replaced or true (any nonzero value) if you want to append to it.
Example: You have a transformation that writes to two target files, one that contains records specific to this run and a second that accumulates information for all transformations of this type. You create a script that tells Map Designer to write or replace the first target file and append records to the second.
Clear Records
Clears record values from the target record buffer. The default value is True.This parameter is ignored if the target connector of the action is not the target connector (for example the Reject connector).Performing a ChangeTarget action on the Reject connector does not clear the record buffer.
Plus Sign Usage in String Syntax
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".
Example 1
ChangeTarget Connection String:
"+File=C:\MyData\testfile.asc"
If the path uses spaces, three options are recommended, in descending order as a best practice:
Use a variable based on a macro for the path string.
Map to a network share name without spaces.
Enclose the path in single quotation marks: "+File='C:\My Data\testfile.asc'"
Example 2
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:
"+File=" & MyVariable & ".asc"
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.
Example 3
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:
"File=xyzzy.out;RecordSeparator=""\x0d\x0a"""
Note:  You must use the \xdd escape sequence for each hexadecimal character. In addition to hexadecimal strings, C-language style escape sequences for some common control characters are also supported. These include carriage return (\r), tab (\t), and new line or line feed (\n).
The following is equivalent to the previous example:
"File=xyzzy.out;RecordSeparator=""\r\n"""
Tip...  To make this action conditional, pass an empty connection string and it does not fire.
Clear Action
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.
Parameters
Description
Source/Target Name
Select Source or Target for which you want the buffer to be cleared.
Record Layout
Select the source or target record for which the buffer must be cleared.In most cases, the only choice is Record1 (R1). If you have specified multiple target record layouts and recognition rules, each record is listed as a choice in the list.
Clear Fields
Boolean expression that indicates whether or not record layout field values are cleared. The default is True. Select False if you do not want to clear field values.
Clear Buffer
Boolean expression that indicates whether or not buffered output records are cleared. The default is True. Select False if you do not want to clear the record memory buffer.
Example
 
Situation
You are storing accumulated data in a running total field. When you get a new header record, it indicates that the data following pertains to a different department. The running total must begin again from nothing.
Action
1. Define a record layout with recognition rules that specify the header record, and name the layout as Department.
2. Select he AfterEveryRecord event in the Department Event Handlers list in the tree structure.
3. Select the Clear action.
4. Set the Source/Target Name parameter to Target.
5. Set the Record Layout parameter to Record1 (R1).
6. Set the Clear Fields and Clear Buffer parameters to True.
Result
When the Department record is read, the Clear action clears the Dependent information fields and the target buffer.
You can decide to clear only the field values and not the target buffer. Then, you can write data in the target buffer to a different field, such as Department Info History. In this case, you will set Clear Fields to True and Clear Buffer to False.
Remarks
Use this action for single record type files. For hierarchical files, see Clear Tree Action.
Clear Tree Action
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.
Parameters
Description
Source/Target Name
Clears the contents of a target record buffer and initializes non-null fields with "0" values.
Required.
Record Layout
Name of the source/target layout whose buffer you want cleared.
Required.
Clear Fields
Boolean expression that indicates whether or not record layout field values are cleared. The default is True. Select False if you do not want to clear field values.
Clear Buffer
Boolean expression that indicates whether or not buffered output records are cleared. The default is True. Select False if you do not want to clear the record memory buffer.
Example
 
Situation
Before the sample map runs, a target XML file contains fields that are placeholders to store multiple records and the source file structure. When the transformation is successful, records are written to the appropriate fields and grouped in a hierarchical format.
Action
1. Set up a Source OnDataChange1 event to define the actions to occur when the Data Change Monitor value changes. Choose OnDataChange1 in the R1 Event Handlers list.
2. Select the Clear Tree action.
3. Set the Source/Target Name parameter to Target.
4. Set the Record Layout parameter to Dependent Info.
5. Set the Clear Fields and Clear Buffer parameters to True.
Result
When the Dependent Info record is read, the ClearTree action clears the current tree, clearing the Dependent Info fields and the target buffer.
You can decide to clear only the field values and not the target buffer. Then, you can write data in the target buffer to a different field, such as Dependent Info History. In this case, you must set Clear Fields to True and Clear Buffer to False.
ClearInitialize 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.
Parameters
Description
Target Name
Clears the contents of a target or reject record buffer and initializes non-null fields with zero values. Select Target or Reject as required.
Record Layout
Select the record for which you want to clear the buffer.
If you have specified multiple target record layouts and recognition rules, then each layout is listed as a choice in the list.
Default Value
Click /download/attachments/24975495/SelectIcon_WithinField.png?version=1&modificationDate=1487964701082&api=v2 and specify an expression to define the initial value for non-null fields. Useful in situations where 0 is not a good initial value.
An expression can be any expression that does not reference fields. For example, "1.25", "Date()", "Serial()".
Example
 
Situation
The target field must not be initialized with a null value, because the results returned to the target are null.
Action
This example shows a mapping expression for a target field "TrgField".
TrgField = Targets(0).Fields("SrcField") + Sources(0).Fields("SrcField")
Result
When a field is initialized with the ClearInitialize Action, the result is the value of "SrcField" added to the value of "TrgField."
ClearMap Action
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.
Parameters
Description
Target Name
Select Target or Reject.
Record Layout
Select the record that must be cleared and have the data written to it.
If you have specified multiple target record layouts and recognition rules, then each layout is listed as a choice in the list.
Count
Expression to evaluate the repetition count (enter a numeric value). Type 0 to suppress the Insert or a 1 to write the Insert.
Note:  If you mention variable names in this parameter, you must have declared them as Private or Public. Local variables are not recognized in Count. You may enter the variable names in Global Variables, or declare the variable name with the Private or Public keyword in your statement.
Writing simple SQL expressions here (and in Counter Variable) gives you precise control over the data that is written. This is useful when writing records in Update Mode.
Ways to Use the Count Parameter
Leave the parameter blank (defaults to 1, writing data to the Target).
Use a Private or Public variable (not Local variables, which are not recognized here).
Use literal numerics (for example, type a 3 to write the data 3 times).
Use any expression that includes a numeric return value.
Counter Variable
Name of the variable used to store the current repetition count.Make that the variable has been declared Private or Public (see the note in Count).
ClearMapPut Record Action
The ClearMapPutRecord action is the default operation done automatically by Map Designer when a transformation is run without any events being specified.
When a new record is read, the following occurs:
Clear - Data stored in the target memory buffer from the previous record is cleared.
Map - Executes the target field expressions and maps the results of the expressions to the target buffer.
Put - Takes data from the target buffer and writes to the file or table.
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.
Parameters
Description
Target Name
Select Target or Reject.
Record Layout
Select the record to clear and to write data to it.
If you have specified multiple target record layouts and recognition rules, then each layout is listed as a choice in the list.
Count
Type any positive integer, or expression that evaluates to a positive integer. This is used to evaluate the repetition count (enter a numeric value). Specify 0 to suppress the Insert, or a 1 to write the Insert.
Note:  If you mention variable names in this parameter, you must have declared them as Private or Public. Local variables are not recognized in Count. You may enter the variable names in Global Variables, or declare the variable name with the Private or Public keyword in your statement.
Writing simple SQL expressions here (and in Counter Variable) gives you precise control over the data that is written. This is useful, for instance, when writing records in Update Mode.
Ways to Use the Count Parameter
Leave the parameter blank (defaults to 1, writing data to the target).
Use a Private or Public variable (not local variables, which are not recognized here).
Use literal numerics (for example, enter a 3 to write the data 3 times).
Use any expression that includes a numeric return value.
Counter Variable
Name of the variable used to store the current repetition count.Make sure that the variable has been declared Private or Public (see note in Count).
Buffered
If you select True, the output record is appended to the memory target buffer (instead of written to the physical target file). The default option is False.
Note:  When combining the fields of two record types, on AfterEveryRecord of the first record type, the buffered property has to be set to True in the ClearMapPut action.
Example
 
Situation
A source file with two record types must be combined into one record in the target.
Action
1. Create record layouts for both record types with recognition rules to distinguish them.
2. Map all the fields from both source record layouts into a single target record layout.
3. Select the AfterEveryRecord event from the Record Layout Events of the second source record layout.
4. Select the ClearMapPut Record action.
5. Set the Target Name parameter to Target.
6. Set the Record Layout parameter to R1.
Result
When the transformation is run, data from the first record type is held in the memory buffer. When a record of the second record type is read, it joins the data from the other record in the buffer, is assigned to appropriate fields, and all the data is written as one record.
Execute Action
The Execute event action executes an expression that you specify. Execute can also be used to call external .dll files and ActiveX components.
Parameters
Description
Expression
Type the expression that you want to execute or click /download/attachments/24975501/SelectIcon_WithinField.png?version=1&modificationDate=1487964742267&api=v2 to specify the expression using the EZscript Expressions editor
Note:  Stored procedures and ActiveX components must be called normally using standard expression syntax.
LogMessage Action
The LogMessage event action writes a message to the error and event log file.
Parameters
Description
Message
Type the message that must be returned to the log file.
Example
 
Situation
Source data that may contain more data in a text field than fits into the target field. Due to this, truncation may occur. The transformation must not stop, but want to know when truncation occurs.
Action
Select the OnTruncationError target event.
Select the LogMessage Action.
Type a message such as "Data has been truncated" in the Message parameter.
Result
Every time data is truncated due to a lack of space in the target field, the message "Data has been truncated" is written to the error and event log.
LogTargetRecord Action
The LogTargetRecord action writes current target record field values into the error and event log file. It is useful for diagnostic purposes.
Tip...  Before following these instructions, you must turn on debugging. In the map file > Configuration tab, in the Logging section, select the Debug Messages option so that the LogTargetRecord action writes messages to your log file.
 
Parameters
Description
Target Name
Select Target or Reject.
Record Layout
Select the record name.
Field Count
Type the number of target record fields that must be written to the log file or click /download/attachments/24975505/SelectIcon_WithinField.png?version=1&modificationDate=1487964767664&api=v2 to specify an expression using EZscript.The default setting is to print all fields.
Map Action
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.
Parameters
Description
Target Name
Select Target or Reject.
Record Layout
Select the record that you want to map.
Count
Type the expression to evaluate the repetition count (enter a numeric value) or click /download/attachments/24975507/SelectIcon_WithinField.png?version=1&modificationDate=1487964782063&api=v2 to specify the expression using the EZscript Expression editor. Specify 0 to suppress the Insert, or a 1 to write the Insert.
Note:  If you mention variable names in this parameter, you must have declared them as Private or Public. Local variables are not recognized in Count. You may enter the variable names in Global Variables, or declare the variable name with the Private or Public keyword in your statement.
Writing simple SQL expressions (and in Counter Variable field) gives you precise control over the data that is written. This is useful, for instance, when writing records in Update mode.
Ways to Use the Count Parameter
Leave the parameter blank (defaults to 1, writing data to the target).
Use a Private or Public variable (not Local variables that are not recognized).
Use literal numerics (for example, enter a 3 to write the data 3 times).
Use any expression that includes a numeric return value.
Counter Variable
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).
MapPut Record Action
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.
Parameters
Description
Target Name
Select Target or Reject.
Record Layout
Select the required record.
Count
Type an integer to evaluate the repetition count (enter a numeric value) for the number of records to be written out for a single record read or click /download/attachments/24975509/dotdotdot.png?version=1&modificationDate=1490821969497&api=v2 to specify the expression using the EZscript Expression editor.Specify 0 to suppress the Put, or a 1 to write the Put.
Note:  The Count parameter is a variable (the variable is already declared). If you mention variable names in this parameter, you must have declared them as Private or Public. Local variables are not recognized in Count. You may enter the variable names in Global Variables, or declare the variable name with the Private or Public keyword in your statement.
Writing simple SQL expressions here (and in Counter Variable) give you precise control over what data is written. Useful when updating records in Update Mode.
Counter Variable
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).
Buffered
If you select Yes, the output record is appended to the memory buffer (instead of written to the physical target file). The default value is No.
Put Record Action
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).
Parameters
Description
Target Name
Select Target or Reject.
Record Layout
Select the record that you want to map.
Count
Type the expression to evaluate the repetition count (enter a numeric value) or click /download/attachments/24975511/dotdotdot.png?version=1&modificationDate=1490822469595&api=v2 to specify the expression using the EZscript Expression editor.Specify 0 to suppress the Insert, or a 1 to write the Insert.
Note:  If you mention variable names in this parameter, you must have declared them as Private or Public. Local variables are not recognized in Count. You may enter the variable names in Global Variables, or declare the variable name with the Private or Public keyword in your statement.
Writing simple SQL expressions (and in Counter Variable field) gives you precise control over the data that is written. This is useful, for instance, when writing records in Update mode.
Ways to Use the Count Parameter
Leave the parameter blank (defaults to 1, writing data to the target).
Use a Private or Public variable (not Local variables that are not recognized).
Use literal numerics (for example, enter a 3 to write the data 3 times).
Use any expression that includes a numeric return value.
Counter Variable
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).
Buffered
If you select Yes, the output record is appended to the memory buffer (instead of written to the physical target file). The default option is No.
Example
To manually set the target buffer, add a Put record action with an Execute expression:
Targets(0)Records(R1)Fields("Name")="PM"
To reference the target buffer, add Targets(0) at the front of a default expression.
Put Tree Action
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.
Parameters
Description
Target Name
Select Target or Reject.
Record Layout
Select the record.
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.
Note:  After the entire tree is written, the AfterPutTree event handler is triggered for the parent record layout.
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:
<Root><Owner><Name/><Address/><City/><State/><Zip/><Vehicles><Vehicle><Year/><Make/> <Model/><Color/><Style/></Vehicle></Vehicles></Owner></Root>
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
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.
QueryStatistic Action
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:
Return transformation statistics at run-time without parsing the log file
Report run-time transformation milestones
Trigger an action based on a transformation milestone
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.
Statistic Name
Execution Statistic
Data Source
Reads
Total records read
source records read
Buffered Writes
Total records buffered
map events (buffered puts)
Writes
Total records written
map events (sent to target)
Inserts
Total records inserted
target
Updates
Total records updated
target
Deletes
Total records deleted
target
Discards
Total records discarded
map events
Rejects
Total records rejected
map events
Errors
Total error count
target (associated with the Writes event action and includes unhandled errors only.)
RowCounts
Total reject records written
map events (sent to reject file/table)
 
Parameters
Description
Statistic Name
Select the statistic query that you want to run:
Reads
Writes
Buffered Writes
Discards
Rejects
Errors
RowCount
Inserts
Deleted
Updates
Result Variable
Type the name of the variable that contains the value you want to return.
Tip...  Public process-level variables are not supported. If you need to pass the return value to a public process-level variable, in this parameter first specify a global variable that is not public and then use an EZscript to assign the required value of this variable.
Example
 
Situation
At run-time, when is the first 100 records written to the target.
Action
This example shows a mapping expression for a target field TrgField.
1. Set the Statistic Name to Writes.
2. Set the Result Variable Name to var.
3. Set the Target Field expression contents for TrgField to:
If var = 100 Then
"Record100"
End If
Result
After the 100th record writes to the target, "Record100" writes to "TrgField" in the 101st target record.
Returns
Buffered Writes Deletes
Discards Errors
Inserts Reads
Rejects RowCount
Updates Writes
Resume Action
The Resume event action causes the transformation to continue normally after an error is detected.
There are no parameters or Resume action.
Example
 
Situation
Overflow errors must not interrupt the transformation process.
Action
Select the OnOverflowError event.
Select the Resume action.
Result
When a numeric field is encountered in the source data that has too many digits to fit in the target field, the transformation ignores the problem and continues converting the rest of the records.
Terminate 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.
Parameters
Description
Message
Type the message that must be written into the error and event log when the transformation is terminated.
Example
 
Situation
The transformation must end if it encounters a record that cannot be written to the target table.
Action
Select the OnDiscard event.Select the Terminate action.Type the message "A record was discarded." for the Message parameter.
Result
When the transformation is run, records are written normally to the target file until a record is discarded for any reason. At that time, the message "A record was discarded." is written in the error and event log, and the transformation ends. Any AfterTransformation event actions are executed and normal transformation termination routines will occur.
TraceOff Action
The TraceOff event action turns off error tracing and returns the logging to its normal state. This is for single-mode connectors only.
Note:  Before following these instructions, you must turn on debugging. To do this, in the Map file, go to the Configuration tab > Logging section, and select the Debug Messages option. This action writes messages to the log file.
There are no parameters to be specified for the TraceOff action.
Example
 
Situation
Need detailed information about what happens to the records immediately after the data changes in an account number field.
Action
TraceOff is used after the TraceOn event.
Choose the TraceOff action after TraceOn so that trace logging is turned off after the monitored record is read.
Result
When the data in the account number changes, detailed messages are written to the error and event log about what happened to the data. Once the TraceOff action is applied, logging returns to normal (before a new record is read).
If you want to troubleshoot a transformation that is failing and not producing detailed log messages, follow these steps.
To set up debugging
1. Clear the log file if required.
2. Open the map file, go to the Configuration tab > Logging section, and select the following:
Debug Messages
Flush log to disk after each message
3. This sets error logging to check debug messages and to return all messages to the log.
4. In the Advanced view, click Source Events in the tree on the left pane, and click /download/attachments/24975521/Map_Mapping_AddField_Icon.png?version=1&modificationDate=1487964966677&api=v2.
The Add Events dialog box is displayed.
5. Select BeforeEveryRecord and AfterEveryRecord, and then click Next.
The Event Actions and Parameters screen is displayed.
6. For the BeforeEveryRecord event name, add the TraceOn action.
7. For the AfterEveryRecord event name, add the TraceOff action.The TraceOn and TraceOff actions send more details about each record transformation to the log.
8. Run the transformation and observe the memory usage.
9. Review the error log messages to determine the reason for the transformation failure.
TraceOn Action
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.
Note:  Before following these instructions, you must turn on debugging. To do this, in the Map file, go to the Configuration tab > Logging section, and select the Debug Messages option. This action writes messages to the log file.
There are no parameters to be specified for the TraceOn action.
Validate Record 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.
Parameters
Description
Source/Target Name
Select the required option:
Source
Target
Reject
Record Layout
Select the record that you want to validate.
Example
 
Situation
The database consists of records of clients throughout the nation. Create a mailing list to for the central Austin, Texas market (ZIP Code 78701).
Action
Set ValidateRecord to the expression: ZIP Code = 78701
Result
Writes only the records that has the zip code as 78701 to the target.
Multimode Event Actions
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:
Multimode Event Actions List
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.
Name
Description
ClearMapInsert Record Action
Clears the target record buffer, executes any map expressions, and inserts records into the target table.
Create Index Action
Creates new indexes with attributes specified in action parameters. Often used in a BeforeTransformation event.
Create Table Action
Creates new tables with attributes specified in action parameters. Often used in a BeforeTransformation event. It can be placed after a Drop action event in a transformation that is designed to be run multiple times. In this scenario, the existing target tables with old data are dropped and then tables are created for new data.
Delete Record Action
Deletes records from multiple tables specified in the action parameters. Write to multiple tables by using this action in a BeforeTransformation event.
Drop Table Action
Drops tables specified in the action parameters. Often used in a BeforeTransformation event. It can be placed before a CreateTable Action event in a transformation that is 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.
Insert Record Action
Acts as a query statement so that records can be inserted into specified tables. Write to multiple tables by using this action in a BeforeTransformation event.
MapInsert Record Action
Executes target field expressions, assigns the resulting data to the target buffer, and inserts records into the target tables.
SQL File Action
Calls the specified SQL files to be used in a transformation.
SQL Statement Action
Includes code that contains one or more SQL statements. These statements are written to trigger various events within the transformation, such as the Insert Action or Delete Action events. SQL commands, such as Truncate or Create Procedure, can be called in SQL Statement Action code.
Update Record Action
Updates records in multiple tables specified in the action parameters. Use this action to write to multiple tables in a BeforeTransformation event.
Upsert Record 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.
ClearMapInsert Record Action
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.
Parameters
Description
Target Name
Select Target or Reject.
Record Layout
Select the record. To insert to more than one target layout, add additional event actions to your transformation.
Table Name
Name of table. To insert to more than one target table, add additional event actions to your transformation.
SQL script as a target: See your SQL application to get table names, since no list is available for SQL script.
Count
Expression to evaluate the repetition count (enter a numeric value). Specify 0 to suppress the Insert, or a 1 to write the Insert.
Note:  If you mention variable names in this parameter, you must have declared them as Private or Public. Local variables are not recognized in Count. You may enter the variable names in Global Variables, or declare the variable name with the Private or Public keyword in your statement.
Writing simple SQL expressions here (and in Counter Variable) gives you precise control over the data that is written. This is useful, for instance, when writing records in Update Mode.
Ways to Use the Count Parameter
Leave the parameter blank (defaults to 1, writing data to the target).
Use a Private or Public variable (not Local variables, which are not recognized here).
Use literal numerics (for example, enter a 3 to write the data 3 times).
Use any expression that includes a numeric return value.
Counter Variable
Name of the variable used to store the current repetition count.Make sure that the variable has been declared Private or Public (see Note in Count).
Create Index Action
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.
Note:  If single-mode connectors are used in a transformation and this action is selected, it is ignored in the transformation process.
 
Parameters
Description
Target Name
Select Target or Reject.
Record Layout
Select the record. To use more than one target layout, add additional event actions to your transformation.
Table Name
Name of table to create an index. To create an index in more than one target table, add additional event actions to your transformation.
SQL Script as a target: Refer to your SQL application to get table names, since no list is available for SQL Script.
Index Name
Type the name of the index that must be created pr click /download/attachments/24975533/SelectIcon_WithinField.png?version=1&modificationDate=1487965112258&api=v2 to open the EZscript Expressions editor and specify an SQL statement to name the index.
Unique
Select True for a unique key. Else, select False.
Create Table Action
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
Note:  If single-mode connectors are used in a transformation and this action is selected, it is ignored in the transformation process.
 
Parameters
Descriptions
Target Name
Select Target or Reject.
Record Layout
Select the record for which the table must be added. To add more than one target layout, add additional event actions to your transformation.
Table Name
Name of table to create. To create more than one target table, add additional event actions to your transformation.
SQL Script as a target: See your SQL application to get table names, since no list is available for SQL Script.
Delete Record Action
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.
Parameters
Description
Target Name
Select Target or Reject.
Record Layout
Select the record to delete. To delete more than one target layout, add additional event actions to your transformation.
Table Name
Name of the table from which records are deleted. To select more than one target table, add additional event actions to your transformation.
For SQL Script as a target, see SQL application to get table names, since no list is available for SQL Script.
Count
Expression to evaluate the repetition count (enter a numeric value). Specify 0 to suppress the Insert, or a 1 to write the Insert.
Note:  If you mention variable names in this parameter, you must have declared them as Private or Public. Local variables are not recognized in Count. You may enter the variable names in Global Variables, or declare the variable name with the Private or Public keyword in your statement.
Writing simple SQL expressions here (and in Counter Variable) gives you precise control over the data that is written. This is useful, for instance, when writing records in Update Mode.
Ways to Use the Count Parameter
Leave the parameter blank (defaults to 1, writing data to the target).
Use a Private or Public variable (not Local variables, which are not recognized here).
Use literal numerics (for example, enter a 3 to write the data 3 times)
Use any expression that includes a numeric return value.
Counter Variable
Name of the variable used to store the current repetition count.
Make sure that the variable has been declared Private or Public (see Note in Count).
Not required.
See Also: Search for the keywords “Counter Variable” in the online help.
Drop Table Action
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.
Note:  If single-mode connectors are used in a transformation and this action is selected, it is ignored in the transformation Execute action.
This action executes an expression that you specify. Execute can also be used to call external .dll files and ActiveX components.
Parameters
Description
Target Name
Select Target or Reject.
Table Name
Name of table to drop. To drop more than one target table, add additional event actions to your transformation.
SQL Script as a target: See the SQL application to get table names, since no list is available for SQL Script.
Insert Record Action
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.
Parameters
Description
Target Name
Select Target or Reject.
Record Layout
Select the record. To use more than one target layout, add additional event actions to your transformation.
Table Name
Name of table to insert records. To insert to more than one target table, add additional event actions to your transformation.
For SQL Script as a target, see the SQL application to get table names, since no list is available for SQL Script.
Count
Expression to evaluate the repetition count (enter a numeric value). Specify 0 to suppress the Insert, or a 1 to write the Insert.
Note:  If you mention variable names in this parameter, you must have declared them as Private or Public. Local variables are not recognized in Count. You may enter the variable names in Global Variables, or declare the variable name with the Private or Public keyword in your statement.
Writing simple SQL expressions here (and in Counter Variable) gives you precise control over the data that is written. This is useful, for instance, when writing records in Update Mode.
Ways to Use the Count Parameter
Leave the parameter blank (defaults to 1, writing data to the Target).
Use a Private or Public variable (not Local variables, which are not recognized here).
Use literal numerics (for example, enter a 3 to write the data 3 times).
Use any expression that includes a numeric return value.
Counter Variable
Name of the variable used to store the current repetition count.Make sure that the variable has been declared Private or Public (see Note in Count).
MapInsert Record Action
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.
Parameters
Description
Target Name
Select Target or Reject.
Record Layout
Select the record. To insert to more than one target layout, add additional event actions to your transformation.
Table Name
Name of the table in which to insert records. To insert to more than one table, add additional event actions to your transformation.
SQL Script as a target: See the SQL application to get table names, since no list is available for SQL Script.
Count
Expression to evaluate the repetition count (enter a numeric value). Specify 0 to suppress the Insert, or a 1 to write the Insert.
Note:  If you mention variable names in this parameter, you must have declared them as Private or Public. Local variables are not recognized in Count. You may enter the variable names in Global Variables, or declare the variable name with the Private or Public keyword in your statement.
Writing simple SQL expressions here (and in Counter Variable) gives you precise control over the data that is written. This is useful, for instance, when writing records in Update Mode.
Ways to Use the Count Parameter
Leave the parameter blank (defaults to 1, writing data to the target).
Use a Private or Public variable (not Local variables, which are not recognized here).
Use literal numerics (for example, enter a 3 to write the data 3 times).
Use any expression that includes a numeric return value.
Counter Variable
Name of the variable used to store the current repetition count. Make sure that the variable has been declared Private or Public (see Note in Count)
SQL File Action
The SQL File event action may only be used by multimode connectors. It calls specified SQL files to be used in a transformation.
Parameters
Description
Target Name
Select Target or Reject.
File Name
Specify the Name of the SQL files to be called.
Count
Expression to evaluate the repetition count (enter a numeric value). Specify 0 to suppress the Insert, or a 1 to write the Insert.
Note:  If you mention variable names in this parameter, you must have declared them as Private or Public. Local variables are not recognized in Count. You may enter the variable names in Global Variables, or declare the variable name with the Private or Public keyword in your statement.
Writing simple SQL expressions (and in Counter Variable) gives you precise control over the data that is written. This is useful, for instance, when writing records in Update Mode.
Ways to Use the Count Parameter
Leave the parameter blank (defaults to 1, writing data to the target).
Use a Private or Public variable (not Local variables, which are not recognized here).
Use literal numerics (for example, enter a 3 to write the data three times).
Use any expression that includes a numeric return value.
Counter Variable
Name of the variable used to store the current repetition count.Make sure that the variable has been declared Private or Public (see Note in Count).
SQL Statement Action
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:
DJX(statement)
For multiple lines or block statements, the syntax is:
DJXBegin
[Insert your multiline expression here]
DJXEnd
 
Parameters
Description
Target Name
Select Target or Reject.
SQL Statement
Type the SQL statement to be run.
Count
Expression to evaluate the repetition count (enter a numeric value). Specify 0 to suppress the Insert, or a 1 to write the Insert.
Note:  If you mention variable names in this parameter, you must have declared them as Private or Public. Local variables are not recognized in Count. You may enter the variable names in Global Variables, or declare the variable name with the Private or Public keyword in your statement.
Writing simple SQL expressions (and in Counter Variable) gives you precise control over the data that is written. This is useful, for instance, when writing records in Update Mode.
Ways to Use the Count Parameter
Leave the parameter blank (defaults to 1, writing data to the target).
Use a Private or Public variable (not Local variables, which are not recognized here).
Use literal numerics (for example, enter a 3 to write the data 3 times).
Use any expression that includes a numeric return value.
Counter Variable
Name of the variable used to store the current repetition count.Make sure that the variable has been declared Private or Public (see Note in Count).
Update Record Action
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:
Action Key fields set to Yes
A match in the source
 
Parameters
Description
Target Name
Select Target or Reject.
Record Layout
Select the record that you want to update. To update to more than one target layout, add additional event actions to your transformation.
Table Name
Name of table to update. To update to more than one target table, add additional event actions to your transformation.
SQL Script as a target – See your SQL application to get table names, since no list is available for SQL Script.
Count
Expression to evaluate the repetition count (enter a numeric value). Specify 0 to suppress the Insert, or a 1 to write the Insert.
Note:  If you mention variable names in this parameter, you must have declared them as Private or Public. Local variables are not recognized in Count. You may enter the variable names in Global Variables, or declare the variable name with the Private or Public keyword in your statement.
Writing simple SQL expressions (and in Counter Variable) gives you precise control over what data is written. This is useful, for instance, when writing records in Update Mode.
Ways to Use the Count Parameter
Leave the parameter blank (defaults to 1, writing data to the target).
Use a Private or Public variable (not Local variables, which are not recognized here).
Use literal numerics (for example, enter a 3 to write the data 3 times).
Use any expression that includes a numeric return value.
Counter Variable
Name of the variable used to store the current repetition count.Make sure that the variable has been declared Private or Public (see Note in Count).
Upsert Record Action
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.
Parameters
Description
Target Name
Select Target or Reject.
Record Layout
Select the record that you want to write.
Table Name
Names of tables to write.
SQL Script as a target – See your SQL application to get table names, since no list is available for SQL Script.
Count
Expression to evaluate the repetition count (enter a numeric value). Specify 0 to suppress the Insert, or a 1 to write the Insert.
Note:  If you mention variable names in this parameter, you must have declared them as Private or Public. Local variables are not recognized in Count. You may enter the variable names in Global Variables, or declare the variable name with the Private or Public keyword in your statement.
Writing simple SQL expressions (and in Counter Variable) gives you precise control over the data that is written. This is useful, for instance, when writing records in Update Mode.
Ways to Use the Count Parameter
Leave the parameter blank (defaults to 1, writing data to the target).
Use a Private or Public variable (not Local variables, which are not recognized here).
Use literal numerics (for example, enter a 3 to write the data 3 times).
Use any expression that includes a numeric return value.
Counter Variable
Name of the variable used to store the current repetition count.Make sure that the variable has been declared Private or Public (see Note in Count).
Managing Events and Actions in Advanced View
In Advanced View, you can add, edit, and delete events for the following nodes in both Source and Target tree view:
All Events node
All Records > Individual Record > Events node
Source Events node (only in Source tree view)
Target Events node (only in Target tree view)
Note:  The procedure to add, edit, and delete events is the same. Only the initial navigation is different.
Managing Events in All Events
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.
To manage events for source or target in All Events node:
1. In the Source or Target tree view, click All Events.
2. To add an event, see Adding Event.
3. To edit an event, see Editing Event.
4. To delete an event, see Deleting Event.
Managing Events for Each Record
To manage events for each record in source or target:
1. In the Source or Target tree view, expand All Records.
2. Expand the required record number. For example, R1, R2, R3, and so on.
3. Click Events.
4. To add an event, see Adding Event.
5. To edit an event, see Editing Event.
6. To delete an event, see Deleting Event.
Managing Events in Source Events
To manage source events:
1. In the Source tree view, click Source Events.
2. To add an event, see Adding Event.
3. To edit an event, see Editing Event.
4. To delete an event, see Deleting Event.
Managing Events in Target Events
To manage target events:
1. In the Target tree view, click Target Events.
2. To add an event, see Adding Event.
3. To edit an event, see Editing Event.
4. To delete an event, see Deleting Event.
Adding Event
To add an event:
1. Click /download/attachments/24975553/Map_Mapping_AddField_Icon.png?version=1&modificationDate=1487965242645&api=v2.
The Add Event(s) window is displayed.
2. If you have clicked /download/attachments/24975553/Map_Mapping_AddField_Icon.png?version=1&modificationDate=1487965242645&api=v2 for All Events node, then select the type of event that you want to add:
Record Events - Select the individual record from the drop-down list. By default, R1 is selected.
Source Events
You can select only one type of event at a time.
This step is not applicable if you have clicked /download/attachments/24975553/Map_Mapping_AddField_Icon.png?version=1&modificationDate=1487965242645&api=v2 for Source Events node or Target Events node.
3. Select the required event handlers or search for the event handler by typing the word in the Search field.
If an event handler is already added, the check box for that event handler is grayed-out and you cannot select it.
4. Click Next.
If you have selected OnDataChange as the event handler, then a page that allows to set the event parameters is displayed. Go to Step 5.
If you have selected any other event handler, then a page that allows you to select actions and specify the parameters for the selected event handlers is displayed. Go to Step 7.
5. For OnDataChange event handlers, specify the following for each event handler:
Data Change Monitor Expression
Trigger Suppression
6. Click Next.
A page that allows you to select actions and specify the parameters for the selected event handlers is displayed.
7. In the Action section, click /download/attachments/24975553/Map_Mapping_AddField_Icon.png?version=1&modificationDate=1487965242645&api=v2 to add an action. By default, Abort action is added.
8. If you want to change the Abort action, click the name and from the drop-down list, select the required action.
You can also do the following:
To delete an action, click the action and then click /download/attachments/24975553/Map_Source_Delete_Icon.png?version=1&modificationDate=1490840984952&api=v2.
To copy an action, click the action that you want to copy and then click /download/attachments/24975553/Map_Source_Copy_Icon.png?version=1&modificationDate=1490841023672&api=v2.
To move the order of the actions:
Click /download/attachments/24975553/Map_Source_UpArrow_Icon.png?version=1&modificationDate=1487965242692&api=v2 to move the action up.
Click /download/attachments/24975553/Map_Source_DownArrow_Icon.png?version=1&modificationDate=1487965242755&api=v2 to move the action down.
Note:  The order of actions is important because they will be executed in the order they are listed.
9. After adding the actions, click each action.
The Parameters section displays the relevant parameters for the selected action.
10. For each parameter, click the Value field and specify a value. When you click within the Value field, /download/attachments/24975553/SelectIcon_WithinField.png?version=1&modificationDate=1490079289711&api=v2 or a drop-down list may be available. If you click /download/attachments/24975553/SelectIcon_WithinField.png?version=1&modificationDate=1490079289711&api=v2, you can add an EZscript expression. If you select the drop-down list, you can either select a value or select <Build Expression...> to add an EZscript expression. For information about adding EZscript expressions, see Scripting.
11. Click Finish.
The events are saved.
Editing Event
To edit an event:
1. Click the event that you want to edit and click /download/attachments/24975553/Map_Source_Edit_Icon.png?version=1&modificationDate=1487965242770&api=v2.
The Edit Event window is displayed.
2. Edit the event parameters, actions, and action parameters and click Finish.
For information about the fields, see Adding Event.
The changes are saved.
Deleting Event
To delete an event:
1. Click the event name that you want to delete and click /download/attachments/24975553/Map_Mapping_DeleteField_Icon.png?version=1&modificationDate=1487965242677&api=v2.
A message asking for confirmation is displayed.
2. Click Yes.
The selected event is deleted.