Web Service Response Cache using VB Script (VBS) and SharePoint - SharePoint Development & Administration + InfoPath

Saturday, July 7, 2012

Web Service Response Cache using VB Script (VBS) and SharePoint

I recently wanted to display data from a Web Service on a page in SharePoint using XSL.  The Web Service was external to SharePoint, which resulted in the Data View web Part being unable to obtain a valid result required to allow the XSL template to be built using features of SharePoint Designer.  A separate issue was that the method used to query the web service hosted on my external server consumed resources on the external application and took around 5 seconds to receive a response from the query.  Using a Data View web part connected directly to the Web Service to display the result would require that the data is obtained every time the page loads, impacting on server performance.

A solution was to create a process outside of SharePoint that consumes the web Service and saves the output to an XML file on the SharePoint Site.  This file was then included as an XML Data Source in the Data View Web Part, allowing the XSL template to be built and the results to remain dynamic.  The script runs periodically to consume the web Service and update the XML file on the SharePoint Site with the new data.

The Process Broken Down:

Consume the Web Service:
The fist component of the VB Script connects to the Web Service using SOAP and obtains the result (as XML). A script written by Ken Hughes was used to consume the Web Service.

Some minor modifications were made to the script to configure it for my scenario, such as updating the URL to the Web Service and the query XML to be sent as part of the request. 

Save the Result to an XML File:
I included an additional function to save the output to an XML file (below).  This function is called from the script after obtaining a result from the Web Service in the result is valid.  The result is saved locally to an XML file, which can then be uploaded to SharePoint for use as an XML data source.

    Function saveXML(byval fileName, byval XMLOutput)
        'create the File System Object
        Set myFSObject = CreateObject("Scripting.FileSystemObject")
        'Open the text File for Writing (existing text is overritten if the file exists)
        Set txtFile = myFSObject.OpenTextFile(fileName, 2, True)
        'Write the output to the file
        'Close the file
        'Release the File System Object from memory
        SET myFSObject = NOTHING
    End Function

Publish the XML file to a SharePoint site using Frontpage RPC:
The next part of the script then publishes the file to the SharePoint site using FrontPage RPC.  Publishing using this method allows an account to be specified in the script to access the SharePoint Site and also allows the file to be added to SharePoint without relying on using the UNC path to the site/library in SharePoint which is not always available from a server.

The following response to a thread on Stack Overflow (posted by user457338) includes VB Script functions that allows a file to be published to a SharePoint site using the Frontpage RPC service.

This allowed the result from the web Service (which had been saved to an XML file), to be uploaded/published to the SharePoint Site, overwriting the existing file it is has been published previously.  Each the script is run, the results are updated and re-published to SharePoint allowing the data displayed by the Data View web Part to remain dynamic.  The script is set to run on the server periodically as a scheduled task allowing the data displayed in SharePoint to be updated dynamically each time the script runs.

VBScript Web Services / SOAP
 A reference sheet with various tutorials, examples, threads and other resources relating to consuming a SOAP web service using VBScript (VBS).

Share this article:
Stumble This Delicious
submit to reddit

No comments:

Post a Comment