Using the SSIS OData Source Connector With SharePoint Online Authentication

Last week, Microsoft released the OData Source for Microsoft SQL Server 2012 . What is it? It allows SQL Server Integration Services (SSIS) to use an OData feed as a first class citizen data source in the same manner as SQL Server, Oracle, etc. Until now it was necessary to code OData connections using the script object.

This matters to those of us in the SharePoint world because any SharePoint list data can be expressed as OData.

I’ve written before about how SharePoint data can be extracted into a data warehouse using SSIS and the SharePoint List Source and Destination Adapters, available from CodePlex. These adapters plug in to SSIS and wrapper the SharePoint SOAP web services, and therefore do not need to be installed on a SharePoint server. We have used them for years, and they work very well, however, they are a CodePlex project, and therefore not fully supported.

These CodePlex adapters have more recently been bumping into another limitation. While SOAP web services are supported in Office 365, the adapters don’t support the Office 365 authentication mechanism, which effectively renders them useless. The OData services require the same authentication, but the the new OData Source supports it. It’s also an official Microsoft product, and is fully supported.

There is, however a trick to getting it working. Once you install the OData Source, you open up SQL Server Data Tools, open an SSIS project, and add or edit a data flow task. In the SSIS Toolbox,  You should see the OData Source.

image

Drag the tool on to the design surface, and double click to configure it. You’ll first need to configure an OData Connection Manager, and you’ll do that by clicking the New button.

image

Give the connection a name. The connection will be common to all lists and libraries within a site, so something based on the name of the site is likely appropriate. The Service document location is the OData endpoint. It takes the form of the URL of the site, along with the suffix /_vti_bin/listdata.svc. If the connection is on premises, you can use Windows Authentication, but if it is Office 365, you must use a stored name and password.

image

If you are using Office 365, and you click Test Connection at this point, you’ll receive an error “Test connection failed –> The remote server returned an error: (400) Bad Request.”

image

This is due to Office 365’s “unique” authentication mechanism. In order to authenticate to Office 365, you must first select the “All” button in the toolbar, and set the value of “Microsoft Online Services Authentication” to true.

image

This option may not be available to you. If the Online Services Authentication option is disabled, or greyed out, as it was for me when I first tried to use it, it’s because a prerequisite is missing. In order to authenticate to Office 365, the machine must have the SharePoint Server 2013 Client Components SDK installed on it.

Once the client components are in place, and the option is selected, the data source should be able to connect to the source, and the connection manager can be closed. Lists are exposed as Collections, so if you want to work with list data, you can then select the list from the list of Collections.

image

At this point, the data source will act like any other SSIS data source, you can select and transform columns at will. More importantly, this will help you get SharePoint data both on-prem and in the cloud into a central data warehouse.

66 thoughts on “Using the SSIS OData Source Connector With SharePoint Online Authentication

  1. BW

    I can connect to my SharePoint 365 list from SSIS, but I don’t see any choice fields. Any ideas on how to pull those in?

  2. jpw@unlimitedviz.com Post author

    I imagine that your problem is that you’re allowing multiple values in your choice fields. OData doesn’t support that type. If you can disallow multiple values, you may have more luck.

  3. joshualeond

    Thanks for the blog, it was very helpful. Do you know if there is any way to capture the Attachment status (True/False) with the OData connection?

  4. Dan

    Our organisation uses OData connections to our flashy new Data Hub, i assume its Sharepoint 365. The documentation I have been provided works when i connect from Excel or PowerBI but no matter what I can’t connect from SSIS. We use basic auth, I’ve switched the MS Online Services Auth option to true – still unauthorized.

    On the documentation i’ve been provided there is an extra step for Excel, where we change the URL that the Basic Auth applies to – there are two options “https://domain.com/xxx.svc” and “https://domain.com” we have to switch it to the root without the service doc name. I think this is what’s causing my problem but I have no idea of the settings in SSIS to do the same?

  5. sqlrookie

    Hello sir,

    Does O data source both read and write to SharePoint via SSIS package or do we need something else to write to SharePoint. Can you please advise.

  6. yogi

    Hi

    i am getting following error message, when i test the connection

    Test connection failed -> For security reasons DTD is prohibited in this XML document. To enable DTD processing set the DtdProcessing property on XmlReaderSettings to Parse and pass the settings into XmlReader.Create

  7. Srini

    Thank you for the info. I could able connect to my SharePoint 365 online list from SSIS, and execute the package in my machine. But, when I have deployed to SQL server (windows2012 R2-OS, SQlServer2016). Following error triggered. Any ideas what could be the issue?

    “Failed to initialize SharePoint.Client. Runtime. The exception message was: Could not load file or assembly ‘Microsoft.SharePoint.Client.Runtime, version=15.0.0.0, Culture=neutral, PublickKey Token=71e9bce111e9429c’ or one of its dependencies, The system cannot find the file specified”.

    Regards
    Srini

  8. MMcCoy

    Hello,

    This article is great, and I have been able to preview the data using this method, but when I attempt to run the package in VS I receive the following error message:

    “Exception deserializing the package “The package failed to load due to error 0xC0011008 “Error loading from XML. No further detailed error information can be specified for this problem because no Events object was passed where detailed error information can be stored.”. This occurs when CPackage::LoadFromXML fails.”.

  9. Alexander

    Hi,
    I got this to work great on our company’s sharepoint. However, it seems as for our customer, claims based authentication create another hurdle. Any ideas on how to deal with that?
    Thanks a lot in advance
    Alexander

  10. Imran

    Hello Sir,

    This is really nice blog to import Sharepoint online list data into SQL server….

    However i was wondering if there is any components like “OData Destination” to push the SQL data into the Sharepoint?

    Kindly help me with the methods of achieving my requirement…
    Thanks..

  11. John White Post author

    Hi Imran. The OData solution is read only. There is a Codeplex solution “SharePoint list source and destination” that is read write, but it won’t work with SharePoint online.

  12. David Early

    John,

    I have my OData Source connection to Office 365 set up. The test connection works and I see data in the preview. I have the 32 bit and the 64 bit SharePoint client dlls but whenever I run my package (in Visual Studio, from Integration Services in SSMS or as a SQL Job) I get the same error
    cannot acquire a managed connection from the runtime connection manager
    I have extensively researched this issue online but none of the ‘solutions’ work for me. Any suggestions gratefully accepted.

  13. Sarwa

    Hi John,

    As a tenant admin (xxxx@xxxx.onmicrosoft.com), I am able to connect SPO site but as a site admin login credentials, I am getting the below error. Could you please check what changes are required from my end.

    The partner returned a bad sign-in name or password error. For more information, see Federation Error-handling scenarios. (Microsoft.SqlServer.integrationServices.ODataConectionManager)

  14. harsheta

    Hi John,

    when connecting to sharepoint list collection having claim based authentication , i am not able to view microsoft online service dropdown , can you please help me wih this.

Leave a Reply

Your email address will not be published.