Tuesday, November 22, 2011

Native sFTP transmission in PeopleSoft

Running Peopletools 8.51.x. A lot has been mentioned about sFTP support in PT 8.51, so today decided to give that a shot. For the POC I am trying to send a extract ascii text file (developed using XMLP eTEXT) via sFTP to a target location.

  1. Setup URL Definition.
    1. My URL has the form sftp://servername/destination_directory. Did not specify the port number, which generally is 22.
    2. Setup URL properties. In my case AUTHTYPE is password, as I am using a basic userid/password type of authentication. Other types like using private/public keys is also available, but I haven't tried that yet. (I did try it here)
    3. I encrypted the password using the password encryption option available on this page.
      1. Note: Though the password is encrypted online, the actual value is stored in a plain text field in the db, so can be queried using a query tool (PS_PT_URL_PROPS, is the name of the table where the properties are stored)
  2. Now test this via the PeopleTools Test Utility page. Once you this working proceed to the next step.
  3. Develop an AE program and add a peoplecode step.
  4. The following code will do the trick
 &returncode = PutAttachment(URL.MY_URL, &filename, &fullpathtofile);

where MY_URL is the URL definition created above
&filename is the filename as in test.txt
and &fullpathtofile is the complete path as in C:\temp\test.txt

That's all, works like a charm, so no more 3rd party products.

Thanks
Deepak.

10 comments:

  1. While migrating the URL definition from one database to another using Application Designer, the URL properties are not copied. So a DMS script or re-keying of the properties is necessary to update the target db.

    ReplyDelete
  2. Replies
    1. Not sure I follow your question Sujith?

      Delete
    2. If i want to read a file from a sftp, what i have to do?

      Delete
    3. use GetAttachment function

      Delete
  3. I am getting &returncode - 9...
    Afetr checking in Peoplebooks this is what i found

    Cannot locate file.

    The following are some possible situations where %Attachment_FileNotFound could be returned:

    Remote file not found.

    Failed to read remote file.

    But the file is there in location...
    Do we need to give &pathtofile differently while working in remote desktop connection?

    Please help..

    ReplyDelete
    Replies
    1. I am using this method on a windows server (using SQL Server db). Not sure if you are using a Windows Server too or Unix. I am not sure if there are any other settings required for Unix server, but this has worked for me either using absolute path to a named drive letter or even UNC path.
      Verify if the file is closed and not being used elsewhere.

      Delete
  4. Local string &local_path1 = "C:\Users\dumpaala.vasu\Images\pic1.JPG";
    Local string &targetfilename1 = "pic1.JPG";
    Local string &extension = Right(&local_path1, 4);
    MessageBox(0, "", 0, 0, &extension, 0);
    If &extension = ".JPG" Then
    &RetCode = PutAttachment(URL.MASS_IMG_SFTP, &targetfilename1, &local_path1);

    ReplyDelete
  5. This is the code I am using and we are using linux server...
    Can you please share the process for linux server

    ReplyDelete
  6. What method or file attachment should I use for sending file from local machine(not app server) to sftp?

    ReplyDelete