SAP Cloud Integration(CPI) – Split records by field value and sending with dynamic file name.

Dear Reader,

This blog describes how to split a message into multiple output messages and send them with a dynamic file name. I am using “dynamic” because the file name to be used in the target server are determined at the runtime. And because I could not find any resource that perfectly applies to my scenario, I am writing this blog. If you have any questions or suggestions to improve my solution, please kindly write in the comment. Thank you for reading in advance.

Scenario:

In my scenario, this is the outbound case, so S4 needs to send the data in the intermediate table through BTP CI to the target server. The data should be separated into different files based on the “Type” field in each line and saved in the target server with the file name including the specific “Type”.

Prerequisites

・Create a CDS view and a OData service in the eclipse and in the S4 respectively.

iFlow components:

・Use a OData adopter to send the data from S4 to BTP.
・Use a SFTP adopter to send the output messages from BTP to the target server.
・Use a message mapping and create a mapping file to match the structure of fields in the message coming from the S4 and being sent to the target server.
・Use a groovy script to put  a line break to let the general splitter easily distinguish the break point.
・Use a general splitter to separate a message by the line break into multiple output messages.
・Use a content modifier to take a “Type” value to be used in the file name and set it to the property.

Steps:

These are the important steps to achieve our goal: splitting a message by the field value and sending with a dynamic file name.

1.Sort the data in the OData adopter by the “Type” field.

Data in xml format produced from OData service is like below.

<Root>
    <Record>
        <Number>0000000001</Number>
        <Type>A</Type>
    </Record>
    <Record>
        <Number>0000000002</Number>
        <Type>A</Type>
    </Record>
    <Record>
        <Number>0000000003</Number>
        <Type>A</Type>
    </Record>
    <Record>
        <Number>0000000004</Number>
        <Type>B</Type>
    </Record>
    <Record>
        <Number>0000000005</Number>
        <Type>B</Type>
    </Record>
    <Record>
        <Number>0000000006</Number>
        <Type>C</Type>
    </Record>
</Root>

2. Write a groovy scrip.

If you use this code, you need to modify the closures like <Root></Root>, <Record></Record>, and <Type></Type> according to your data. And you need to change the length of the field value. In the line 42 ‘type.length()!=1‘, I set 1 because the ‘Type’ field value must have the length 1.

import com.sap.gateway.ip.core.customdev.util.Message;
import java.util.HashMap;

def Message processData(Message message) {
    def body = message.getBody(java.lang.String) as String;
    
    def properties = message.getProperties();
    
    def messageLog = messageLogFactory.getMessageLog(message);
    
    //split the message body by </Record> to take a list of records
    def String[] ref_records = body.split("</Record>", -1);
    
    //split the message body by <Type> to take a list of records which starts with the type field value
    def String[] ref_types1 = body.split("<Type>", -1);
    
    //counter to iterate ref_records
    def counter = 0;
    
    //string buffer to ouput as a property
    def records = new StringBuffer();
    
    //this helps us to see if the type is changed through the iteration of records
    def pre_type = "";

    //ref_records and ref_types shuold have the same length, otherwise, it is a cardinality error
    if ( ref_records.length != ref_types1.length ) {
        messageLog.addAttacmentAsString("Error:", "cardinality error", "text/plain");
    }
    
    for(int i=1; i < ref_types1.length; i++){
        //split the ref_types1 by </Type> to take only the type itself.
        String[] ref_types2 = ref_types1[i].split("</Type>", 2);
        String   type       = ref_types2[0];
    
        //initial case. Do nothing but note the 'pre_type' for later use
        if (counter == 0) {
            pre_type = type;
        }
        
        //general case (i.e. 'counter' > 0)
        else {
            
            //the type must have length = 1. otherwise, it's an error.
            if (type.length()!=1) {
                messageLog.addAttachmentAsString("Length error:", type, "text/plain");
            }
            else {
                //if current type is different from the previous type, split records by a line break
                if (pre_type!=type) {
                    records.append("</Record></Root>\n<Root>");
                    pre_type = type;
                    
                //if current type is the same as the previous type, do not split
                } else {
                    records.append("</Record>");
                }
            }
        }
        
        //append the current record
        records.append(ref_records[counter]);
        
        //increment the counter by 1
        counter += 1;
    }

    //the last line needs to get closed.
    records.append("</Record>");
    
    //</Root> should be appended as well
    records.append(ref_records[counter]);
    
    //set the records with line breaks to the message body
    message.setBody(records);
    
    return message;
}

This script should output the message like below.

<?xml version="1.0" encoding="UTF-8"?>
<Root><Record><Number>0000000001</Number><Type>A</Type></Record><Record><Number>0000000002</Number><Type>A</Type></Record><Record><Number>0000000003</Number><Type>A</Type></Record></Root>
<Root><Record><Number>0000000004</Number><Type>B</Type></Record><Record><Number>0000000005</Number><Type>B</Type></Record></Root>
<Root><Record><Number>0000000006</Number><Type>C</Type></Record></Root>

3. Configure a general splitter with the line break and the parallel processing mode.

Select the line break and the parallel processing mode. The number of concurrent processes should be more than the number of types. In this case, we only have three types: A, B, C, but if the data contains D and E as well and the maximum number of types is 5, you have to set 5. For now, I set 3 in this demonstration.

This Splitter should ouput three different messages.
The first message with records having Type ‘A’.

<?xml version="1.0" encoding="UTF-8"?>
<Root><Record><Number>0000000001</Number><Type>A</Type></Record><Record><Number>0000000002</Number><Type>A</Type></Record><Record><Number>0000000003</Number><Type>A</Type></Record></Root>

The second message with records having Type ‘B’.

<?xml version="1.0" encoding="UTF-8"?>
<Root><Record><Number>0000000004</Number><Type>B</Type></Record><Record><Number>0000000005</Number><Type>B</Type></Record></Root>

The third message with records having Type ‘C’.

<?xml version="1.0" encoding="UTF-8"?>
<Root><Record><Number>0000000006</Number><Type>C</Type></Record></Root>

As you can see, you could split records by the ‘Type’ field. The sequence of the messages may change(the first message could be the one having the type ‘C’), but it does not matter at all as long as all three messages are saved in the target server.

4. Configure a content modifier with the exchange property.

You can specify the field in two ways: //Type or Root/Record/Type. This sets the current ‘Type’ value to the one of the properties named ‘CurrentType’.

5. Set an expression in the file name in the SFTP adopter.

You can access the property by using Expression: ${property.CurrentType}.
I also added a timestamp to avoid overwriting the files.

Note: you can also use this expression in the directory name as required.

6. Check the result in the connectivity test panel.

To check if the files are correctly saved in the target server, in SAP Integration Suite, go to  Integration >ConnectivityTest>{your target server}.

As you can see, you could save the files with the file name including the ‘Type’ accordingly.