The Audit feature tracks data as it flows through your jobs, providing visibility into execution. However, error handling is required when issues occur, such as missing source files, data type mismatches, or target connection failures.
While auditing records what your data did, error handling determines how your job responds to unexpected events. Together, these capabilities ensure your data workflows are both observable and resilient.
Watch the following video to learn more about error handing strategies.
Using a While Loop
A while loop is a workflow object that repeats a sequence of steps as long as a specific condition remains true.
Typically, the steps within the loop eventually change the condition so the workflow can exit. If the condition never changes, the loop will run indefinitely.
To wait for a system-generated file, use a while loop to check for the file using file_exists. If the file is missing, the workflow can enter sleep mode for a set duration before checking again.
To ensure the loop eventually terminates even if the file is never created, add a counter. The loop should check both for the file's existence and that the counter is below a maximum threshold. In each iteration, the workflow sleeps and then increments the counter.
Setting Up Transactions
When loading large datasets or dependent tables (such as sales order and sales order item), you must ensure that either all related rows are loaded or none are.
By default, SAP Data Services loads tables using multiple transactions to optimize performance. The "Rows per commit" option determines how many rows are processed in each transaction.
If a job fails using the default settings, some rows may remain committed in the target table. To prevent this, you can configure SAP Data Services to treat the entire data flow as a single transaction.
Managing Partially Loaded Data
To prevent duplicate rows during a job restart, use one of the following methods:
- Use the Table Comparison transform to identify and insert only missing rows. This is effective for large tables with many rows, such as fact tables.
- Enable Delete data from table before loading or Drop and re-create table in the target table options. This is ideal when changes are extensive relative to the table size.
- Use the auto-correct load feature for tables with fewer rows and more fields, such as dimension tables.
- Execute a Preload SQL command to remove partial updates from the previous failed run before the new load begins.
The auto-correct load option checks for existing rows before inserting new ones. While this ensures accuracy, it can impact performance. Use this for large tables where changes are infrequent.
Preload SQL commands typically delete rows based on a variable set before the initial insertion began, ensuring a clean state for the retry.
| Resources |
|---|
For detailed instructions on preloading SQL commands, refer to the SAP Data Services Designer Guide. |
Handling Missing Values or Rows
SAP Data Services provides dedicated mechanisms for handling data quality issues at both the value and row level:
- Manage missing values using the Validation or Query transforms.
- Manage missing rows (failed insertions) using Overflow file options.
Missing rows occur when data cannot be inserted into the target, often due to primary key constraint violations. Overflow files capture these failed rows for later analysis.
When an overflow file is configured, SAP Data Services writes failed rows to it and logs the file location in the trace log. This information helps identify source data errors or transformation issues. Note that each new job run overwrites the existing overflow file.
To use overflow files, you must specify a file name and choose a format:
- Write data: Allows SAP Data Services to read the overflow file later to cleanse and reload the data.
- Write sql: Generates SQL commands that you can run manually to load the target once the issue is resolved.
Caution
Summary
In this lesson, you have learned how to set up an efficient error handling strategy for your SAP Data Services jobs.
- Prevent errors proactively by using conditions and loops (such as while loops with file_exists) to verify required context before execution, always including exit conditions.
- Implement recovery strategies like transactions, duplicate-row prevention logic, and exception handling to restore database consistency after failures and avoid duplicate data loads.
- Manage transactions carefully by configuring single-transaction data flows for dependent tables to ensure all-or-nothing loading, though this may increase memory usage.
- Handle partial loads using Table Comparison transforms, preload SQL commands, or auto-correct load features to identify and insert only missing rows during job restarts.
- Capture failed rows with overflow files to log insertion failures caused by constraint violations, enabling later analysis and data cleansing for reload.