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.

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

  • Why SAP Data Services

    SAP BusinessObjects Data Services delivers a single enterprise-class solution for Data Integration, Data Quality, Data Profiling, and Text Data Processing that allows us to integrate, transform, improve, and deliver trusted data to critical...

  • Map_CDC_Operation in Data Services

    Map CDC Operation is used to support relational or mainframe changed-data capture (CDC). This transform reads the source DML changes i.e. INSERT/UPDATE/DELETE and reflects the same in the target system. So the transform maps all the source rows...

  • How to use Data Services Reverse Pivot Transformation

    In this article, we will learn how to use SAP Data Services Reverse Pivot Transform. The Reverse Pivot transformation combines data from several rows into one row by creating new columns. For each unique value in a pivot axis column and each...

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

  • How to install SAP BODS - Standalone

    This article is a step by step guide on how to install standalone SAP BODS Batch Job Server, Client Components, Web based Administrative Console and how to configure the same.

  • Fools Guide to BODS - Repository Creation

    In the first chapter of this article we have learnt the very basic of BODS. In this part of the article we will begin with BODS Repository creation process

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