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

By:   |   Updated: 2013-11-21   |   Comments (2)   |   Related: > SharePoint


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.

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.


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();
        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 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 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 name="ows_Last_x0020_Modified" rs:name="Modified" rs:number="31">
   <s:datatype dt:type="datetime" dt:lookup="true" dt:maxLength="8" />
  <s:AttributeType name="ows_Created_x0020_Date" rs:name="Created" rs:number="32">
   <s:datatype dt:type="datetime" dt:lookup="true" dt:maxLength="8" />
    <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" />

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)
 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 Updated: 2013-11-21

get scripts

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

Comments For This Article

Thursday, December 31, 2020 - 4:15:39 AM - Pavel Pawlowski Back To Top (87986)
It looks like the URL you are sending does not exists on the SharePoint. Can you debug the code and check the constructed URL? You can try to put the URL also into to browser to verify it returns the needed XML.

Have you passed proper list/survey GUID and/or Application URL?

Tuesday, December 08, 2020 - 1:31:14 PM - Mike Back To Top (87892)
Thank you very much for your sharing. follow your tips, but get some error. Could you give me some advise? Appreciate !

[Script Component [21]] Error: System.Net.WebException: The remote server returned an error: (404) Not Found.
at Microsoft.SqlServer.Dts.Pipeline.ScriptComponentHost.HandleUserException(Exception e)
at Microsoft.SqlServer.Dts.Pipeline.ScriptComponentHost.PrimeOutput(Int32 outputs, Int32[] outputIDs, PipelineBuffer[] buffers)
at Microsoft.SqlServer.Dts.Pipeline.ManagedComponentHost.HostPrimeOutput(IDTSManagedComponentWrapper100 wrapper, Int32 outputs, Int32[] outputIDs, IDTSBuffer100[] buffers, IntPtr ppBufferWirePacket)

[SSIS.Pipeline] Error: SSIS Error Code DTS_E_PRIMEOUTPUTFAILED. The PrimeOutput method on Script Component returned error code 0x80131509. The component returned a failure code when the pipeline engine called PrimeOutput(). The meaning of the failure code is defined by the component, but the error is fatal and the pipeline stopped executing. There may be error messages posted before this with more information about the failure.


Recommended Reading

Reading SharePoint Lists with Integration Services 2017

Using a SharePoint List as a Data Source in SQL Server Reporting Services 2008 R2

Using SSIS to Automatically Populate a SharePoint List

Understanding SharePoint Exports to Excel

Create Power BI Reports from a SharePoint Online Library

get free sql tips
agree to terms