Create file on SFTP server using Web Service and Rest API from SharePoint Online

Sometimes, we need to deal with third party tools/applications from SharePoint Online. To deal with third party tools/applications, we may need a medium like SFTP server to pass data from SharePoint Online to third party tools/applications. In this blog, we are going to showcase - how to create xml/text file on SFTP server from SharePoint Online.

First, let's understand what's SFTP server and how it can be used to transfer data from SharePoint Online.

What is SFTP server?

SFTP - Secure File Transfer Protocol server, is popular method of transferring files between two remote systems over a secure connection. In this blog, we will see how to transfer data from one remote server (SharePoint Online) to another remote server (Workday).

Create file on SFTP server using Rest API & Web Service in SharePoint Online:

We can follow below steps to create file on SFTP server:

Step 1: Create a page in SharePoint Online and add custom button using HTML and JavaScript file. On Custom button click event, JavaScript file would call web service to create file on SFTP server.

Step 2: Create a web service (c#) using Visual studio.

• Install “SSH.NET” package to deal with the SFTP server.
• Find below code to connect SFTP server.

var client = new SftpClient("Server URL", "User Name", "Password");
client.Connect();

• Then, stores incoming data into memory.

byte[] bArray = Encoding.UTF8.GetBytes(dataURL);
var memory = new MemoryStream(bArray);

• Now, Upload/Create a file on SFTP server.

client.UploadFile(memory, "/Folder path /File Name", null);

• At the end, close the connection with SFTP server.

client.Disconnect();
client.Dispose();

• The complete code for SFTP server integration is as below:

[WebMethod]
[ScriptMethod(ResponseFormat = ResponseFormat.Json)]   
public string UploadFileToFTP(string data)
{
     var client = new SftpClient("Server URL", "User Name", "Password");
     client.Connect();
     byte[] bArray = Encoding.UTF8.GetBytes(dataURL);
     var memory = new MemoryStream(bArray);
     client.UploadFile(memory, "/Folder path /File Name", null);
     client.Disconnect();
     client.Dispose();
}

Step 3: Publish this web service on Microsoft Azure platform.

Step 4: Add following code to JavaScript reference file in SharePoint Online. In this step, we would get data from SharePoint Online list and pass data to web service to create file on SFTP server.

function MoveFileToSFTP()
{
     var DataURL = "'ServerURL'/FTPFileUpload.asmx/UploadFileToFTP";
     var request = new XMLHttpRequest();
     var Data = JSON.stringify(data);
     var params = "data="+Data+"";
     request.open('POST', DataURL, true);
     request.onreadystatechange = function() {
        var responsetext = request.responseText;
 if (request.readyState==4)
 {
    alert("File has been created successfully on SFTP server.");
 }
     };
     request.setRequestHeader("Content-type", "application/x-www-  form-urlencoded");
     request.setRequestHeader("Content-length", params.length);
     request.setRequestHeader("Connection", "close");
     request.send(params);
}

This way, we can upload files to SFTP from SharePoint Online programmatically.

If you have any questions you can reach out our SharePoint Consulting team here.

SharePoint Online Integration with Workday through SFTP

Sometimes, we need to deal with third party tools/applications from SharePoint Online. To deal with third party tools/applications, we may need a medium like SFTP server to pass data from SharePoint Online to third party tools/applications. In this blog, we are going to showcase - how to create xml/text file on SFTP server from SharePoint Online.

First, let's understand what's SFTP server and how it can be used to transfer data from SharePoint Online.

What is SFTP server?

SFTP - Secure File Transfer Protocol server, is popular method of transferring files between two remote systems over a secure connection. In this blog, we will see how to transfer data from one application - SharePoint Online to another remote server application (e.g. Workday).

Create file on SFTP server using Rest API & Web Service in SharePoint Online:

We can follow below steps to create file on SFTP server:

Step 1: Create a page in SharePoint Online and add custom button using HTML and JavaScript file. On Custom button click event, JavaScript file would call web service to create file on SFTP server.

Step 2: Create a web service (c#) using Visual studio.

• Install “SSH.NET” package to deal with the SFTP server.
• Find below code to connect SFTP server.

var client = new SftpClient("Server URL", "User Name", "Password");
client.Connect();

• Then, stores incoming data into memory.

byte[] bArray = Encoding.UTF8.GetBytes(dataURL);
var memory = new MemoryStream(bArray);

• Now, Upload/Create a file on SFTP server.

client.UploadFile(memory, "/Folder path /File Name", null);

• At the end, close the connection with SFTP server.

client.Disconnect();
client.Dispose();

• The complete code for SFTP server integration is as below:

[WebMethod]
[ScriptMethod(ResponseFormat = ResponseFormat.Json)]   
public string UploadFileToFTP(string data)
{
     var client = new SftpClient("Server URL", "User Name", "Password");
     client.Connect();
     byte[] bArray = Encoding.UTF8.GetBytes(dataURL);
     var memory = new MemoryStream(bArray);
     client.UploadFile(memory, "/Folder path /File Name", null);
     client.Disconnect();
     client.Dispose();
}

Step 3: Publish this web service on Microsoft Azure platform.

Step 4: Add following code to JavaScript reference file in SharePoint Online. In this step, we would get data from SharePoint Online list and pass data to web service to create file on SFTP server.

function MoveFileToSFTP()
{
     var DataURL = "'ServerURL'/FTPFileUpload.asmx/UploadFileToFTP";
     var request = new XMLHttpRequest();
     var Data = JSON.stringify(data);
     var params = "data="+Data+"";
     request.open('POST', DataURL, true);
     request.onreadystatechange = function() {
        var responsetext = request.responseText;
 if (request.readyState==4)
 {
    alert("File has been created successfully on SFTP server.");
 }
     };
     request.setRequestHeader("Content-type", "application/x-www-  form-urlencoded");
     request.setRequestHeader("Content-length", params.length);
     request.setRequestHeader("Connection", "close");
     request.send(params);
}

This way, we can upload files to SFTP from SharePoint Online programmatically.

If you have any questions you can reach out our SharePoint Consulting team here.

How to use ASMX services in visual web part development

Use case:

Visual web part needs to fetch data from aspx web service. WSDL url is provided.

Service reference vs Code generation:

There are two ways to add reference to asmx services. You can directly right click the web part project and click on add service reference, click on advance and then choose asmx service reference.

Adding service reference will hard code the service url. So it is suggested that we use WSDL.exe available in .net framework tools to generate a client proxy.

How to use WSDL.exe to generate service proxy?

1. Go to .net command prompt
2. type wsdl /language:csharp /out:c\csfilename.cs

This will automatically generate the cs file provided in out paramter. Add the class to your web part folder and adjust the namespace.
Further Considerations:

1. Url of the service should not be hard coded, it should be coming from web part property.
2. To imlpement this, add a custom property in web part to store service url
3. Add another paramterized constructor to the first class. See example below:
   Default constructor:
    public Spotlight()
           {
               this.Url = "web service url";
           }
   New constructor:
    public Spotlight(string _url)
   {
     this.Url = _url;
   }
4. Generally the first class in wsdl generated proxy cs will be the one where you need to add the new constructor.
5. You can confirm this by looking at the first class and it will be inheriting from
         "System.Web.Services.Protocols.SoapHttpClientProtocol" class.
6. When web part property for service url is blank, use default constructor
7. When web part property for service url is not blank use parameterized constructor.
8. When your service will change, you have to repeat the process to generate proxy and add additional parameterized constructor

Other best practices:

1. Always use proper error handling to make sure exceptions are not visible to end users
2. Always show appropriate message in case of no data to users
3. Always show appropriate message in case of error getting data from service
4. Always use best practice to parse xml data.
5. If possible use xml deserializers to convert xml data into List

Conclusion:
We find it very useful to use WSDL.exe to generate asmx proxy and using constructor injection to initialize the service url from web part property.

If you have any questions you can reach out our SharePoint Consulting team here.

How to use soapUI to review ListData.svc

sIntroduction
SoapUI is a free tool that can be used to test out of the box ListData.svc service provided by sharepoint. This service is available at /_vti_bin/ListData.svc for any of your sharepoint site. Here is how we can try it out using SoapUI.
Step1: Install sopa UI and click on File > New SoapUI Project. Add some project name and check "Add REST Seervice:" like shown in following figure and then click OK

Step2: After clicking on OK another window will open like following, just click on OK

Step3: New REST resource window will appear. In resource name enter site url and in resource path/endpoint provide list/document library name and click on "Extract params" button. Following is the screen that shows sample data.

Step4:That will automatically create a new request called Request 1.This request will automaticlaly detect endpoint and path to your list/document library. If it is not, just click on"-no endpoint set-" and click on add new endpoint. Enter site url in the dialog again.

Step5: Maximize "Request 1" window in soapUI. In botton left corner you will see " ... ", click on it and you will be able to provide sharepoint authentication details there.

Step6: Once you are done with authentication details you can click on execute icon at top left corner of "request 1" window (marked in above figure) to execute the request and you will be able to see response at right hand side window.
Step7: ListData.svc can also return json data in response out of the box. Just try to provide accept = application/json like shown in figure below:

If you have any questions you can reach out our SharePoint Consulting team here.

jQuery.find not working in IE7 for xml data

As part of troubleshooting a shareoint functionality, we found that jQuery.find not working.

We were using jQuery.ajax to call a sharepoint web service and in success function, finding an element in response xml to render it on div.

We fixed this and there are couple of options to fix this that we found from stackoverflow.

1. add dataType: 'xml​' in jQuery.ajax and that will fix it.

2. in response, check for browser is IE7 and variable that is having data is blank, do it with another method, here is javascript code:


if ($.browser.msie && liHtml == "") {
xml = new ActiveXObject("Microsoft.XMLDOM");
xml.async = false;
xml.loadXML(response);
liHtml = $("XmlTagNameToFind",xml).text();
}

"liHtml" is the variable which has jQuery.find().text() output.

I hope this will help you as well.

If you have any questions you can reach out our SharePoint Consulting team here.