Exception Handling While Reading Multiple XML Files in Data Services

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:

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

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 <b>While Loop</b> object 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, let's 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.

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