Learn more about SQL Server tools

mssqltips logo
 

Tutorials          DBA          Dev          BI          Career          Categories          Webcasts          Whitepapers          Today's Tip          Join

Tutorials      DBA      Dev      BI      Categories      Webcasts

DBA    Dev    BI    Categories

 

Reading SharePoint Surveys and Lists using a SQL Server Integration Services SSIS Package


By:   |   Read Comments   |   Related Tips: > Sharepoint

Attend this free live MSSQLTips webcast

Tips and Tricks to Growing Your SQL Server Estate from SMB to Enterprise Scale
Thursday, June 28, 2018 - click here to learn more


Problem

You may need to read SharePoint list or survey's data into SQL Server for further processing. For reading SharePoint lists there are the SharePoint List Source and Destination adapters on CodePlex. The SharePoint Surveys are in fact a list.  Unfortunately they are a special kind of list, which are not supported by the adapters provided on CodePlex. In order to read the Surveys data you need to use alternate methods.  Check out this tip to learn more.

Solution
This tip will demonstrate an alternate method of loading SharePoint Surveys and Lists data using an SSIS package. We will read the data in XML format, store the data in a table and then query the results using T-SQL.

To retrieve the XML data we will utilize SharePoint Team Services and in particular we will use the owssvr.dll ISAPI extension which implements the Team Services functionality.

Getting Information about the Survey or List

As mentioned above we will use the owssvr.dll ISAPI extension to get the Survey or List data in XML format though the URL Protocol. To be able to use it, we need the URL of the SharePoint application and GUID of the list. The easiest way to retrieve this information is to use the "Export to Spreadsheet" function as shown below on the individual survey.

Export to Spreadsheet

This process generates an owssvr.iqy file which can be used to get the data into Excel, but also contains all the information we need. In this file, you can find the SharePointApplication URL and also the SharePointListName which is the GUID of the list we need.

WEB
1
https://www.mydomain.com/8/FeedbackPE/_vti_bin/owssvr.dll?XMLDATA=1&List={7975F329-B54D-43AD-B0A8-4AE071EC59A5}&View={C990B331-BEB0-4B9A-A41D-83C3616E19EB}&RowLimit=0&RootFolder=%2f8%2fFeedbackPE%2fLists%2fFeedback
Selection={7975F329-B54D-43AD-B0A8-4AE071EC59A5}-{C990B331-BEB0-4B9A-A41D-83C3616E19EB}
EditWebPage=
Formatting=None
PreFormattedTextToColumns=True
ConsecutiveDelimitersAsOne=True
SingleBlockTextImport=False
DisableDateRecognition=False
DisableRedirections=False
SharePointApplication=https://www.mydomain.com/8/FeedbackPE/_vti_bin
SharePointListView={C990B331-BEB0-4B9A-A41D-83C3616E19EB}
SharePointListName={7975F329-B54D-43AD-B0A8-4AE071EC59A5}
RootFolder=/8/FeedbackPE/Lists/Feedback

Building the SSIS Data Flow

The data flow is quite simple. We will use Script Component as Source and OLEDB as destination. The Script Component retrieves the data from SharePoint and the OLEDB Destination stores it into a working table.  For more information about SSIS check out this tutorial and see the sample SSIS Package at the end of this tip.

Survey Data Flow

The Script Component will have a single output column ListXMLData of Unicode text stream (DT_NTEXT) data type and we will pass two project parameters ListURL and ListGUID as ReadOnlyVariables.

Script Component Columns


Script Component Variables

Once the column and variables are defined, we can write the script to get the XML Data. The script will first build the URL according the URL Protocol and then invoke HttpWebRequest to get the XML data.

If we use the https protocol to access the SharePoint site, then it will probably be necessary to assign a custom certificate validation method to ServicePointManager, otherwise the site certificate will not be validated and the http request will fail.

Please also note, that in the case of Surveys, you will have to be a Survey administrator to be able to read all the responses. Without administrator rights on the Survey, you will not be able to retrieve all responses.

For the complete data retrieval we implement the internal SharePointList Class and then call the SharePointList.GetSharePointListXml() method in the CreateNewOutputRows() method of the Script Component. The GetSharePointListXML method returns the List XML data as string, which we pass as output to parse for our final table.

internal class SharePointList
{
    /// <summary>
    /// Reads the SharePoint List XML Data
    /// </summary>
    /// <param name="siteUrl">Site URL</param>
    /// <param name="listGuid">GUID of the List</param>
    /// <returns>SharePoint List XML Data</returns>
    public static string GetSharePointListXml(string siteUrl, string listGuid)
    {
        return GetSharePointListViewXml(siteUrl, listGuid, null, null);
        //return doc.OuterXml;
    }
    /// <summary>
    /// Reads the SharePoint List XML Data
    /// </summary>
    /// <param name="siteUrl">Site URL</param>
    /// <param name="listGuid">GUID of the List</param>
    /// <param name="viewGuid">GUID of the View</param>
    /// <param name="fieldsList">List of the Fields to retrieve</param>
    /// <returns>SharePoint List XML Data</returns>
    public static string GetSharePointListViewXml(string siteUrl, string listGuid, string viewGuid, string fieldsList)
    {
        StringBuilder urlBuilder = new StringBuilder(siteUrl.Length + listGuid.Length + (viewGuid == null ? 0 : viewGuid.Length) + (fieldsList == null ? 0 : fieldsList.Length) + 100);
        //Add Site and the Display Command to URL and the List GUID
        urlBuilder.AppendFormat("{0}{1}/owssvr.dll?Cmd=Display&List={2}", siteUrl, siteUrl.EndsWith("/") ? "" : "/", QuoteWithBrackets(listGuid));
        //Add View guid into the URL if provided
        if (!string.IsNullOrEmpty(viewGuid))
            urlBuilder.AppendFormat("&View={0}", QuoteWithBrackets(viewGuid));
        urlBuilder.Append("&XMLDATA=TRUE"); //This instruct the owssvr.dll to return XML data we need.
        //Add output columns or * representing all columns (if no columns list is provided) into the URL
        urlBuilder.AppendFormat("&Query={0}", string.IsNullOrEmpty(fieldsList) ? "*" : fieldsList);
        return GetSharePointListUrlXml(urlBuilder.ToString());
    }
    /// <summary>
    /// Reads the SharePoint List XML Data
    /// </summary>
    /// <param name="listUrl">ULR of the List to read data</param>
    /// <returns>SharePoint List XML Data</returns>
    public static string GetSharePointListUrlXml(string listUrl)
    {
        WebResponse response;
        //Use our internal class which always returns true to certifiicate validation so we always trust our site
        ServicePointManager.ServerCertificateValidationCallback = new RemoteCertificateValidationCallback(CertificateOverride.RemoteCertificateValidationCallback);
        //Web Request to the SharePoint URL to retrieve the Surveys Content
        HttpWebRequest request = (HttpWebRequest)System.Net.HttpWebRequest.Create(listUrl);
        //We need to assign a cookie container to the HttpWebRequest as withouth cookies the request agains SharePoint will not work.
        CookieContainer cookieContainer = new CookieContainer();
        request.CookieContainer = cookieContainer;
        //We are doing the request udenr the credential of the user running the package.
        request.Credentials = System.Net.CredentialCache.DefaultNetworkCredentials;
        //Get the response and read the whole content (xml data)
        response = request.GetResponse();
        string xmlData;
        using (StreamReader sr = new StreamReader(response.GetResponseStream()))
        {
            xmlData = sr.ReadToEnd();
            sr.Close();
        }
        return xmlData;
    }
    /// <summary>
    /// Adds brackets around the input string if the brackets are missing
    /// </summary>
    /// <param name="input">input string</param>
    /// <returns>output string with brackets on the beginning and end</returns>
    private static string QuoteWithBrackets(string input)
    {
        string output = input;
        if (!output.StartsWith("{"))
            output = "{" + output;
        if (!output.EndsWith("}"))
            output += "}";
        return output;
    }

    /// <summary>
    /// Internal class with the callback method for certificate validation. We return true, so we trust our site without any
    /// additional validation
    /// </summary>
    internal class CertificateOverride
    {
        public static bool RemoteCertificateValidationCallback(object sender, X509Certificate certificate, X509Chain chaing, SslPolicyErrors sslPolicyErrors)
        {
            return true;
        }
    }
}

Processing the XML data

The XML data we retrieve and store in the table will look like the sample below (sample output is shortened). As we can see that at the beginning there is the row schema definition followed by the actual data.

<xml xmlns:s="uuid:BDC6E3F0-6DA3-11d1-A2A3-00AA00C14882" xmlns:dt="uuid:C2F41010-65B3-11d1-A29F-00AA00C14882" xmlns:rs="urn:schemas-microsoft-com:rowset" xmlns:z="#RowsetSchema">
  <s:Schema id="RowsetSchema">
    <s:ElementType name="row" content="eltOnly" rs:CommandTimeout="30">
      <s:AttributeType name="ows_ContentTypeId" rs:name="Content Type ID" rs:number="1">
        <s:datatype dt:type="int" dt:maxLength="512" />
      </s:AttributeType>
      <s:AttributeType name="ows_Who_x0020_is_x0020_the_x0020_per" rs:name="Who has given the response" rs:number="4">
        <s:datatype dt:type="string" dt:lookup="true" dt:maxLength="512" />
      </s:AttributeType>
      <s:AttributeType name="ows_What_x0020_is_x0020_the_x0020_pr" rs:name="What is the project or service this feedback concerns?" rs:number="5">
        <s:datatype dt:type="string" dt:maxLength="1073741823" />
      </s:AttributeType>
  <s:AttributeType name="ows_Last_x0020_Modified" rs:name="Modified" rs:number="31">
   <s:datatype dt:type="datetime" dt:lookup="true" dt:maxLength="8" />
  </s:AttributeType>
  <s:AttributeType name="ows_Created_x0020_Date" rs:name="Created" rs:number="32">
   <s:datatype dt:type="datetime" dt:lookup="true" dt:maxLength="8" />
  </s:AttributeType>
 </s:ElementType>
  </s:Schema>
  <rs:data>
    <z:row ows_ContentTypeId="0x0029F375794DB5AD43B0A84AE071EC59A5" ows_Who_x0020_is_x0020_the_x0020_per="2381;#[email protected]" ows_What_x0020_is_x0020_the_x0020_pr="Project1" ows_Last_x0020_Modified="8;#2013-06-12 15:31:42" ows_Created_x0020_Date="8;#2013-06-12 15:31:42" />
    <z:row ows_ContentTypeId="0x0029F375794DB5AD43B0A84AE071EC59A5" ows_Who_x0020_is_x0020_the_x0020_per="2572;#[email protected]" ows_What_x0020_is_x0020_the_x0020_pr="Project2" ows_Last_x0020_Modified="9;#2013-06-12 15:40:06" ows_Created_x0020_Date="9;#2013-06-12 15:40:06" />
    <z:row ows_ContentTypeId="0x0029F375794DB5AD43B0A84AE071EC59A5" ows_Who_x0020_is_x0020_the_x0020_per="1936;#[email protected]" ows_What_x0020_is_x0020_the_x0020_pr="Project3" ows_Last_x0020_Modified="10;#2013-06-12 15:58:21" ows_Created_x0020_Date="10;#2013-06-12 15:58:21" />
  </rs:data>
</xml>

Based on the schema and XML structure, we can invoke a T-SQL query against the XML data to get individual feedback records as separate rows.

WITH XMLNAMESPACES('urn:schemas-microsoft-com:rowset' AS rs,  '#RowsetSchema' AS z)
SELECT  
 T.RowData.value('(@ows_Who_x0020_is_x0020_the_x0020_per)[1]', 'varchar(255)') AS WhoHasGivenFeedback
 ,T.RowData.value('(@ows_What_x0020_is_x0020_the_x0020_pe)[1]', 'varchar(128)') AS Project
 ,T.RowData.value('(@ows_Created)[1]', 'datetime') AS CreatedTMS
 ,T.RowData.value('(@ows_Modified)[1]', 'datetime') AS ModifiedTMS
FROM dbo.ListXmlData ld
CROSS APPLY ld.ListXmlData.nodes('/xml/rs:data/z:row') T(RowData)

It produces a result like the one below. This output can be stored in your final table for further processing.

WhoHasGivenFeedback       Project   CreatedTMS              ModifiedTMS
------------------------- --------- ----------------------- -----------------------
2381;#[email protected]  Project1  2013-06-12 09:54:34.000 2013-06-12 09:54:34.000
2572;#[email protected]  Project2  2013-06-12 10:41:10.000 2013-06-12 10:41:10.000
1936;#[email protected]  Project3  2013-06-12 15:25:22.000 2013-06-12 15:25:22.000
Next Steps


Last Update:


next webcast button


next tip button



About the author
MSSQLTips author Pavel Pawlowski Pavel Pawlowski is Lead BI Specialist in Tieto with focus on Microsoft SQL Server and Microsoft BI platform.

View all my tips





Post a comment or let the author know this tip helped.

All comments are reviewed, so stay on subject or we may delete your comment. Note: your email address is not published. Required fields are marked with an asterisk (*).

*Name    *Email    Email me updates 


Signup for our newsletter
 I agree by submitting my data to receive communications, account updates and/or special offers about SQL Server from MSSQLTips and/or its Sponsors. I have read the privacy statement and understand I may unsubscribe at any time.



    



Learn more about SQL Server tools