How to import data from text file via PI to ERP

[stradav]

Summary

Our initial situation is as follows: we do have text files with comma separated values which we want to import to our ERP system, automatically. This files are on a SFTP-server. So our PI-system should look every now and then if new files are available on the SFTP-server. If so, gonna catch them all and send them to our ERP system. One file can contain one to many entries, which should send to our ERP system in one call.

iFlow

 

Precondition

To start over, we do need a PI-system (surprise!), in our case a SAP Netweaver Process Integration 7.50, a working ESR connection to our SAP ERP-system to configure the SPROXY and a SFTP-server we can use for testing the scenario.

Let's Start

Let's start with a close look to our data file, and the data types we want to import.

1;Aragorn;Gondor

2;Boromir;Gondor

3;Galadriel;Valinor

4;Samweis;Hobbington

Our file has four entries and every entry consists of Id, name and lands. Quite simple so far. So let's start over and create the corresponding data-types. We define three data types, a DataEntry which represents one singe line of our data file, a DataList and a DataListExternal. DataListExternal is the data type we get from our data file from the SFTP-server, DataList is the data type we send to our ERP-system. We have two data types for the same thing, because they could potentially differ, if we do some message mapping and so on.

DataEntry

DataList

And because adding objects to our scenario is so much fun. We additionally create some Message Types. As you can see, XML namespace is empty. This is with full intention and conviction, because it caused some trouble with the message mapping later.

Message Types

And of course we also need Service Interfaces. So let's go ahead and create some. And keep in mind, as always, we need one for inbound and one for outbound communication.

Service Interface Inbound

Service Interface Operation Inbound

Service Interface Outbound

Service Interface Operation Outbound

And just for completion we also add a simple Message Mapping and a Operational Mapping. We map our source DataListExternal to the target DataList.

Message Mapping

Message Mapping Definition

Operation Mapping

Operation Mapping Definition

At the end of the day, our namespace looks like this and is full of happy little design objects.

 

Now we do have all the necessary objects in our Enterprise Service Browser. We can now switch to our SAP ERP system and call transaction SPROXY. At Source -> ESR -> SWCs we find our namespace and the freshly created objects. With a right mouse click on our Service Interface SI_ImportData, we can generate the Proxy Interface and the Implementing Class, which holds the method which is called when the PI system is sending data to the ERP system.

SPROXY

Proxy

Class Method

We are almost there. So now we need a Communication Channel, a receiver adapter for sending messages to our ERP system. We use a SOAP adapter for sending HTTP messages.

Communication Channel Proxy Receiver

Communication Channel Proxy Receiver

And not to forget, we need a Communication Channel to fetch the data files from the SFTP-server. We use a SFTP adapter for this. But there is more. This little adapter has to do some serious magic to provide the right data for the subsequent processes. At first we create a Communication Channel called CC_SFTP_Sender and fill in all the stuff this little fellow needs for communication, like the server address, port, username, fingerprint and so on. We have to specify a filename and a directory. This is the place and file the adapter is looking for when connecting to the SFTP server. We can specify whether the file has to be deleted after processing or not and we can choose a polling interval. For testing purposes we set the polling interval to a few seconds, because time is money.

Communication Channel SFTP Sender

Communication Channel SFTP Settings

Now this is the part where the magic happens. For the subsequent processing we do need a XML file. So we have to convert our comma separated data file to XML. For this task we can use the MessageTransformBean, which we can call as module in the processing sequence.

Communication Channel SFTP Modules

We configure the module with the following parameters. The parameter xml.fieldNames has to match exactly the elements of the data type we specified before, because it is case sensitive.

Module Key	Parameter Name	Parameter Value
mt	Transform.Class	com.sap.aii.af.sdk.xi.adapter.Conversion
mt	xml.conversionType	SimplePlain2XML
mt	xml.documentName	DataListExternal
mt	xml.endSeparator	'nl'
mt	xml.fieldNames	Id,name,lands
mt	xml.fieldSeparator	;
mt	xml.processFieldNames	fromConfiguration
mt	xml.structureTitle	DateEntry

And last but not least, we do need an Integrated Configuration where we can go the full circle and everything comes together. We can do this with an iFlow as seen in the first screenshot or "by hand" with the Integration Builder. So we roll up our sleeves and get it done. We create an new Integrated Configuration and use our CC_SFTP_Sender as inbound communication. For our receiver interface we SI_ImportData and our Operation Mapping OM_DataListExternalToDataList and for the outbound processing we use the CC_Proxy_Receiver.

Integrated Configuration

Integrated Configuration

Integrated Configuration

Integrated Configuration

 

And we are done and ready to roll!

Debug, or it didn't happen!

But wait! How do we know, that this is actually working? So we are going back to our ERP system, open our method ZZII_SI_IMPORT_DATA~SI_IMPORT_DATA and place an External Breakpoint there. We can specify the user for the External Breakpoint at More -> Utilities -> Settings -> Debugging.

Debug

We put a file at our SFTP-server and wait for our CC_SFTP_Sender to fetch it from there. After a few seconds our breakpoint hits and the debug editor pops up.

Breakpoint

Input data

There is the standard variable "input" where we can find our data which has been sent. And as we can see, our beloved LotR characters have safely reached their destination.

Why so complicated?

You are right. There is an easier way to do this. If you don't need a message mapping in your scenario, you could simple skip the operations and message mapping and just build the XML in the SFTP adapter. This works quite fine. You have to consider, that documentName and structureTitle have to exactly match the data type. I got troubles with the XML namespace. The adapter printed it like "ns:http://namespace" but the PROXY expected "ns0:http://namespace". I coudn't figure out to edit ns to ns0, so I get rid of the XML-namespaces in the message type definition. This worked for me.

Conclude

It's not rocket science, but took me some time to bring it to work. I would highly apricate your thoughts and comments on the subject, in order to improve the scenario.

For more information on that topic see:

SAP Process Integration