This article will demonstrate loading multiple XML files using SAP Data Services including Exception Handling.

Lets us consider a scenario where we are loading mutiple source XML based files having employee information based on department coming from many geographic locations. So the obvious answer will be, everything remains unchanged and we just use wild-character in the XML file name to process all source files. That's true but whenever it encounters any exceptions the Job terminates and doesn't process the remaining files. So lets check the implementation.

Below is how the Job Looks like:

Job- Multiple Source XML Files

The Job uses four Global Variables for processing as shown below:

Job- Global variables

The Job consists of an Script followed by a While Loop object. The Initialization Script namely Script_Start, will use the wait_for_file function, to capture the list of source XML files and their count. Also we initialize the While loop counter variable to 0.


#Script_Start

wait_for_file('E:\\DS\\*.xml', 1, 1, -1, $file_name_list, $list_size, ',');

#List of XML files
print( $file_name_list );

#Number of XML files
print( $list_size );

#Loop Counter Initialized
$Counter = 0;

Next we will use a While Loop object namely While_Process_XML_Files to process each source XML files individually. The while Condition is : $Counter < $list_size

Below is content of the While Loop object Workspace:

While Loop Workspace

It consists of a Script namely Script_Set_File, used to extract one source XML file name from the file list, to process further. Also the While loop counter variable is incremented.


#Script_Set_File

$Counter = $Counter + 1;

if ( $Counter = 1 )
begin
$XMLFile = replace_substr( substr( $file_name_list
, 1, instr( $file_name_list , ',', 1, $Counter ) - 1 )
, '/', '\\\\' );
end 

else if ( $list_size = $Counter )
begin
$XMLFile = replace_substr( substr( $file_name_list
, instr( $file_name_list , ',', 1, $Counter - 1 ) + 1
, length( $file_name_list ) - instr( $file_name_list 
, ',', 1, $Counter - 1 ) ), '/', '\\\\' );
end 

else 
$XMLFile = replace_substr( substr( $file_name_list 
, instr( $file_name_list , ',', 1, $Counter - 1 ) + 1
, instr( $file_name_list , ',', 1, $Counter ) 
- instr( $file_name_list , ',', 1, $Counter - 1 ) - 1 )
, '/', '\\\\' );

print( $XMLFile );

Here we are using a Custom Function namely INSTR as described in the article SAP Data Services Custom Function. Now the source XML file name to process via the dataflow is set.

With context to Handling XML source files in SAP Data Services, lets keep the dataflow as is. The only change being the XML Source File XML file will use a Global Variable namely, $XMLFile. The existing dataflow will process the XML source files and will load the data to Target DB tables.

Next we have placed the Dataflow within a Try-Catch block to handle XML Exceptions that may occur while processing invalid source XML files. In the Exception Handling workspace of the Catch object we are using a Script namely Script_Move_Err_XML to move the XML erroneous files to an Error Directory for further investigation.

Catch Workspace


#Script_Move_Err_XML

#Move corrupt XML file to Error Directory
print( exec( 'cmd', 'move "' || $XMLFile || '" ' || 'E:\\DS\\Error' , 8 ) );

This is how we can handle exceptions and prevent the Job from termination, while processing multiple XML source files.


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.

  • Data Services Scenario Questions Part 2

    In this tutorial we will discuss some scenario based questions and their solutions using SAP Data Services. This article is meant mainly for Data Services beginners.

  • DENSE RANK in Data Services

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

  • 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...

  • Fools Guide to BODS - Designer

    In our earlier article on Data Services we have learnt how to register the local repository with CMC. In this part of the article we will start using Data Services Designer.

  • Data Services Scenario Questions Part 6

    In this tutorial we will discuss some scenario based questions and their solutions using SAP Data Services. This article is meant mainly for Data Services beginners.

  • Data Services Scenario Questions Part 5

    In this tutorial we will discuss some scenario based questions and their solutions using SAP Data Services. This article is meant mainly for Data Services beginners.

  • Data Services Metadata Query Part 1

    Data Services provides full access to the repository metadata tables and views for metadata analysis. To access this metadata either we can use SQL SELECT statements or use the metadata reporting from Management Console.

  • Error Handling, Recoverability- SAP Data Services

    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.

  • 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...

  • Data Services Scenario Questions Part 4

    In this tutorial we will discuss some scenario based questions and their solutions using SAP Data Services. This article is meant mainly for Data Services beginners.