Skip to Content
Author's profile photo Pranav Malhotra

SFTP File Lookup using User Defined Function

Hello !

In one of the requirements , we have proxy to SuccessFactors(sfsf adapter) scenario in which we need to validate records from proxy with the records in external excel file provided on the sftp server. If the ID(unique no.) is same as provided in the File then we have to send that record else we need to suppress that record on the mapping level .

This can correlate with the following Scenarios: 

1. When we need to get additional data after the sender’s data or when we need to validate some data .

2. When we need to get some dynamic values from secondary sender  .

To achieve this , we need to lookup on the SFTP server for excel File using UDF in the message mapping .

Configuration Required :

com.jcraft.jsch.jar  should be imported .

Procedure:

Before writing the UDF code , we need to import com.jcraft.jsch  external jar file through imported Archive in Enterprise Service Repository(ESR) like this :

Jar file can be found at the below link provided :

https://sourceforge.net/projects/jsch/files/jsch.jar/0.1.54/jsch-0.1.54.jar/download

Next , We need to use this Imported Archive in Message Mapping . for this we go to functions tab of Graphical mapping and add imported archive as:

After doing this , We should import com.jcraft.jsch.* in th UDF as:

Now we need to write following code in the UDF :

        JSch jsch = new JSch(); //Jsch Object
        Session session = null; //For initializing Session
       String Data = null ;
        try {
            session = jsch.getSession("<username>", "<sftp client>", 22);
            session.setPassword("<password>");
            java.util.Properties config = new java.util.Properties();
            config.put("StrictHostKeyChecking", "no"); //if we are not checking fingerprints 
            session.setConfig(config);
            session.connect();
            com.jcraft.jsch.Channel channel = session.openChannel("sftp");
            channel.connect();
            ChannelSftp sftpChannel = (ChannelSftp) channel;
           InputStream stream = sftpChannel.get("/newFile.xls");
           BufferedReader br = new BufferedReader(new InputStreamReader(stream));
      	   StringBuilder response = new StringBuilder();
           String line = "";
               try{  while (line != null) {
                    line = br.readLine();
                    response.append(line);
                    response.append("\n");
                   } 
                  }catch(Exception e)
                    { System.out.print(e); }
            sftpChannel.exit();
            session.disconnect(); //ending Sessions
            Data = response.toString();
                }
             catch (JSchException e) 
                 {
                 e.printStackTrace();  
                 }
            catch (SftpException e) {
                 e.printStackTrace();
                }
               
return Data;

 

In this way we can achieve this requirement  .

 

 

Thankyou  🙂 .

Assigned Tags

      5 Comments
      You must be Logged on to comment or reply to a post.
      Author's profile photo Avinash S. Mavinkurve
      Avinash S. Mavinkurve

      Good Job ....It is so helpful.

      Ty .  🙂

      Author's profile photo Avinash S. Mavinkurve
      Avinash S. Mavinkurve

      good job 🙂

      Author's profile photo Former Member
      Former Member

      great blog.

      helpful.

      Author's profile photo Former Member
      Former Member

      Great work Buddy ..:)

      Author's profile photo Shravan Kumar A
      Shravan Kumar A

      Hi Pranav,

      I have a requirement where in i need to add one file header to a file in append mode .

      The requirement is as follows :

      In a day , SAP sends certain number of IDOCs to XI . For the first IDOC that comes in ,XI has to create a file and later for the rest of the IDOCs ,XI just has to append the file content .

      My file structure should be as follows:

      Header

      Seg1

      Seg2

      seg3

      Seg4

      Seg5

      seg6

      Getting Output as:

      Header

      Seg1

      Seg2

      seg3

      Header

      Seg4

      Seg5

      seg6

      While creating a file in append mode at receiver , the header record getting appended all the time when the Idoc comes.

      Any pointers/suggestions/ideas will be helpful to get the file header only once

       

      Thanks,

      Shravan