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 | ||||
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. |
Event Type | Action | Action Description | Action Parameter | Action Parameter Description |
Record Event | Abort | Aborts 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 command is processed before it is passed to the connector. Select the type of command: • plain text • expression • preprocessor | |||
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. | - | - |
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 aborted. | Logging abort 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 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 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. See also . | |
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 aborted. | Logging abort 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, aborting 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, aborting or terminating when a record is rejected. |
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 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 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 |
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 |
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 |
Event Action Name | Description |
Abort | Causes the transformation to abort. 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 aborting 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. |
Parameters | Description |
Message | Message that is written to the error and event log when the transformation is aborted. 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. |
Situation | Transformation must abort 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 aborted. |
Parameters | Description |
Expression | Select False to trigger an OnAssertionError event. |
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 |
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. |
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. |
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. |
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. |
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. |
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 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()". |
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." |
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). |
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. |
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. |
Parameters | Description |
Expression | Type the expression that you want to execute or click to specify the expression using the EZscript Expressions editor Note: Stored procedures and ActiveX components must be called normally using standard expression syntax. |
Parameters | Description |
Message | Type the message that must be returned to the log file. |
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. |
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 to specify an expression using EZscript.The default setting is to print all fields. |
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 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). |
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 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. |
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 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. |
Parameters | Description |
Target Name | Select Target or Reject. |
Record Layout | Select the record. |
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. |
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 |
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. |
Parameters | Description |
Message | Type the message that must be written into the error and event log when the transformation is terminated. |
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. |
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). |
Parameters | Description |
Source/Target Name | Select the required option: • Source • Target • Reject |
Record Layout | Select the record that you want to validate. |
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. |
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. |
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). |
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 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. |
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. |
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. |
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. |
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). |
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) |
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). |
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). |
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). |
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). |