Error Handling, Recoverability are the important aspects of any ETL tool. Some of the ETL tools have some sort of in-built error-handling and automatic recovery mechanisms in place.

Alternatively we usually design our ETL framework to handle runtime errors or exceptions. The three mains goals for a perfect ETL framework are Error Handling and Logging, Recoverability and Restartability. If a job does not complete properly, we must fix the problems that prevented the successful execution of the job and run the job again. However, during the failed job execution, some dataflows in the job may have completed and some tables may have been loaded, partially loaded, or altered. Therefore, we need to design the ETL jobs to be recoverable i.e. rerun the job and retrieve all the data without duplicate or missing data. There can be various techniques to recover from unsuccessful job executions.

Let us check the in-built features available in SAP Data Services. Recovery mechanisms are available in SAP Data Services for batch jobs only.

Data Services Error Log

Error logs helps us to determine the reason of job execution failure. The Error log lists errors generated by Data Services, by the source or target DBMS, or the operating system. If the execution completed without error, the Error log is blank. On the other hand Trace logs are used to determine where an execution failed. Let us check the significance of the columns in an Error log.

  • Pid- The process thread identification number of the executing thread.
  • Tid- The thread identification number of the executing thread.
  • Number- An Error number prefix followed by a number.
  • TimeStamp The date and time when the thread generated the message.
  • Message An error description that occurred as the thread was executing.

Now lets check the significance of the Error number prefixes:

  • ADM - Administration errors.
  • BAP - BAPI errors.
  • BIW - SAP BW errors.
  • CON - Connection errors. The connection indicated could not be initialized or failed during execution.
  • DBS - Database management system errors.
  • EML - Email errors.
  • FIL - Filespec errors.
  • OPT - Optimization errors.
  • PAR - Parser errors.
  • R3C - SAP connectivity errors.
  • R3S - SAP syntax errors.
  • REP - Repository errors.
  • RES - Resolver errors.
  • RUN - Runtime errors.
  • SCH - Job launcher errors.
  • SRV - Job Server errors.
  • SYS - System exceptions.
  • USR - User function errors.
  • VAL - Validator errors.
  • XRN - Transform errors.

Error Handling for File Formats

During job execution, for flat-file sources Data Services processes one row at a time. We can configure the File Format Editor to identify rows in flat-file sources that contain the following types of errors:

  • Data Conversion Errors — Suppose a field might be defined in the File Format Editor as having a data type of integer but the data encountered is actually varchar.
  • Row Format Errors — In the case of a fixed-width file, the software identifies a row that does not match the expected width value.

In the File Format Editor, the Error Handling set of properties performs the following actions on selection:

  • Log the data-type conversion or row-format warnings to the Error Log.
  • Can limit the number of warnings to log without stopping the job.
  • Capture or write the errors in the Error File.
  • Check for either of the two types of source flatfile error ( Data Conversion & Row Format ).
  • Writes the invalid rows to a specified Error file in the Job Server.
  • Stop processing the source file after reaching a specified number of invalid row count.
  • The error file format is a semicolon-delimited text file.
  • The contents of the error files are:
  1. Source file path and name, as multiple input source files can be processed using the file format in the dataflow.
  2. Row number of the error record in the source file.
  3. Detailed error description for the rejected record.
  4. Column number where the error occurred.
  5. All columns values of the invalid row seperated by semicolons.
  • If the file format's Parallel process thread option is set to any value greater than 0 or {none}, then the row number in source file value will be -1 for the invalid records.


Have a question on this subject?

Ask questions to our expert community members and clear your doubts. Asking question or engaging in technical discussion is both easy and rewarding.

Are you on Twitter?

Start following us. This way we will always keep you updated with what's happening in Data Analytics community. We won't spam you. Promise.

  • SAP BODS Transforms

    This article deals with the various types of transformations available in SAP BODS. Transformations are in-built, optional objects used in dataflow to transform source data to desired output dataset objects available in Local Object Library under...

  • One Stop to SAP BODI/BODS

    BODI Business Objects Data Integrator or BODS Business Objects Data Services is a GUI workspace that allows to create jobs that extracts data from heterogeneous sources, transforms that data using built-in transforms and functions to meet business...

  • RANK in Data Services

    In this article, we will learn how to implement RANK operation in SAP Data Services.

  • SAP Data Services Analytic Functions

    In this tutorial we will learn how to implement Cumulative Sum in SAP Data Services (BODS). Since there is no available in-built Analytic Functions in SAP Data Services, we will achieve the end result by exploiting some in-built Data Services...

  • How to use Data Services Pivot Transformation

    In this article, we will learn how to use SAP Data Services Pivot Transform. The Pivot transformation allows us to change how the relationship between rows is displayed. For each value in each pivot column, Data Services produces a row in the...

  • How to implement SCD Type 3 in Data Services

    In this tutorial we will learn how to implement Slowly Changing Dimension of Type 3 using SAP Data Services. SCD type 3 design is used to store partial history. Here we are only interested to maintain the "current value" and "previous value" of an...

  • Getting Started SAP BODS

    This article is a step by step guide to learn the basic of SAP BODS. Starting from the Basic we will cover the essential topics like SCD implementation, Fact loading, CDC Mechanisms, Persistent Cache, Substitution Parameters, Variables and...

  • DENSE RANK in Data Services

    In this article, we will learn how to implement DENSE RANK operation in SAP Data Services.

  • How to use Lookup and Join in SAP Data Services

    In this tutorial, we will learn how to perform lookup and join in SAP BusinessObjects data services using the Query Transform. In the way, we will also see how to use some of the basic inbuilt functions that are provided in data services. A short...

  • Data Services Scripting Language

    We can use the Data Services Scripting Language to write scripts and custom functions to address complex logical expressions.