Control Append

The Control Append strategy improves upon the basic Append strategy by adding extra steps for better flow control and error handling. Data is staged in an integration table, checked for errors, and only valid records are appended to the target table.

Steps Involved in the Control Append Strategy:

1. Drop and Create Integration Table:
• If the integration table ("I$") already exists, it is dropped.
• A new integration table is created in the staging area with the same attributes as the target table. This table will be used for flow control and will be passed to the Check Knowledge Module (CKM).
2. Insert Data into Integration Table:
• Data from the source and loading tables is inserted into the integration table using a single INSERT/SELECT statement, similar to the Append strategy. This step applies transformations as needed during the loading process.
3. Call the CKM for Flow Control:
• The CKM is invoked to evaluate the data in the integration table against the constraints defined for the target table.
• The CKM identifies erroneous records and:
• Creates an error table ("E$") and inserts the erroneous records into this table.
• Removes the erroneous records from the integration table, ensuring that only valid records remain.
4. Only Valid Data Remains in the Integration Table:
• After the CKM process, the integration table will only contain valid records, ensuring that data can be safely appended to the target table.
5. Remove All Records from the Target Table (Optional):
• The target table can be cleared of all records before appending new data. This step can be made dependent on an option value defined by the designer of the mapping.
6. Append Valid Records to the Target Table:
• The valid records from the integration table are appended to the target table using a single INSERT/SELECT statement.
7. Commit the Transaction:
• After all operations are complete, the transaction is committed to ensure the changes are saved.
8. Drop the Temporary Integration Table:
• The temporary integration table ("I$") is dropped after the data has been successfully loaded into the target table.

 

Error Recycling

1. Purpose of Error Recycling:
• Error recycling is used to reprocess errors from previous mapping executions that have been fixed or updated in subsequent runs.
• For example, if a product ID was missing in the product table during a previous run, but later added by an administrator, the corresponding sales records in the error table can be re-applied during the next execution.
2. How Error Recycling Works:
• The process involves using an extra task in the Integration Knowledge Module (IKM).
• Rejected records from the error table of previous executions are inserted into the integration table before calling the CKM for quality checks.
• This task is typically conditioned by a KM option called RECYCLE_ERRORS, which allows for reprocessing the previously rejected records.
3. Benefits of Error Recycling:
• Error recycling ensures that data quality issues can be resolved in subsequent executions, improving data integrity and reducing the need for manual intervention.