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.

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

  1. Pingback: Friday Five - April 28, 2014 - The Microsoft MVP Award Program Blog - Site Home - MSDN Blogs

  2. Pingback: Spotlighting MVP award program blogs - Canadian IT Manager's Blog - Site Home - TechNet Blogs

  3. Pingback: Using the SSIS OData Source Connector With SharePoint Online Authentication | The White Pages | CompkSoft

  4. Pingback: Using the SSIS OData Source Connector With SharePoint Online Authentication | The White Pages | CompkSoft

  5. Chris Jones

    Please note that this will not work with ADFS federated credentials, as the client components sdk referenced here and used/recognized by the OData Source Connector only supports non-federated authentication (v15 of the client and client runtime DLLs). There is a new SDK that supports federated authentication (http://www.microsoft.com/en-us/download/details.aspx?id=42038) but then was not able to switch the Services Authentication option to true. I even edited the code manually, but the connection still failed. I was able to get a connection working via a simple console app one of my developers put together, but until MS fixes this glaring oversight, SSIS integration remains 1 tiny step out of reach. That is, unless it is MY oversight :-)

  6. Chris Jones

    UPDATE: I copied the 2 required DLLs from the v16 SDK (located in C:\Program Files\Common Files\microsoft shared\Web Server Extensions\16\ISAPI) to the C:\Program Files\Common Files\microsoft shared\Web Server Extensions\15\ISAPI folder (v15 of the SDK) and was able to successfully make the connection.

  7. Chris Jones

    I hate to only be able to say that with my limited SSIS experience, I was able to export data and the choice values were showing in the data. However, for multi-select choice fields and lookups, as well as people pickers, it returned an ID, but even InfoPath has that same issue with lookups returning the ID value. I do agree that in this respect it is rather useless, but I overcame the issue with a few hidden columns that held the text value of the data (written by a workflow but you might be able to use calculated columns)

  8. Max Kenney

    Hi John, Thanks for your post. Thanks also to Chris for your comments. I’m having trouble with connecting to SharePoint online with this method. I’ve also copied over the DLLs as per Chris’ comment but to no Avail. Chris did you do anything else to get it working with federated credentials? Could I also ask what version of VS you’re using, and what versions of the SDK & OData Source (x64 or x86) you’re using?
    Thanks again to both.

  9. Chris Jones

    I am using VS 2012 and the 2 latest versions of the SDKs for on prem and online. All are x64. For what it’s worth, I also added the account used to access to the list with read permissions. I guess I really didn’t take a scientific approach because I did quite a few things prior to getting it to work, so I’m not positive of the exact combo. As for the federated account, the account I used was a company.com account and not an onmicrosoft.com account. Hope this helps.

  10. Max Kenney

    Chris, thanks so much for getting back to me. So I’ve finally got it to work thanks to your last comment “the account I used was a company.com account and not an onmicrosoft.com account. Hope this helps.”. It certainly did help. Switching from an onmicrosoft.com account to a Company.com account resulted in a successful connection (to another site). I’m now hoping to test all the installations on a fresh environment so I know exactly what components I need to install on the server.

  11. karen

    the odata source works great to download data from a list to a database or flat file destination but how do I upload data from a sql database to a cloud list effortlessly… is there a tool in ssis that will connect to claims based authentication in the cloud

  12. Mark Heptinstall

    Great article, although I’m able to successfully create a connection and test it, when I drop an OData Source into my Data Flow Task and select the connection I get the error message “Unable to load the collection for the connection…”. Has anyone ever come across this and knows how to resolve it?

  13. Anonymous

    Chris references “2 required dlls” that were copied from the v16 to v15 folder . What are the 2 dll’s?

  14. J. B. Rawlings

    I’m getting the following error after working through authentication issues: Test connection failed while parsing the XML document because it is not a valid OData service document.

    Can I assume it found the listdata.svc file but couldn’t parse it? Any thoughts?

  15. Chris Jones

    This is in response to a question from anon. above. The 2 dlls to move are Microsoft.SharePoint.Client.dll and Microsoft.SharePoint.Client.Runtime.dll. Copying them to the “15” folder as detailed above allows for Claims/Federated authentication in SSIS.

    Cyberpine, Im not sure of SSRS. In any case, SSRS is much more integrated with on-prem SP2013. You could use an SSIS pkg to extract your list data from O365/SPO to SQL and then use SSRS to report off of that.

  16. John

    Very nice read and exactly what I have been looking for. Sadly, I can not go for it. Installed MS SQL Server 2014 BI (64 bit) and ODataSourceForSQLServer2014-amd64, but no chance to see any Odata Source component in SSIS development. Nothing in the common section, no connectors available within data flow window. I tried to add elements to the toolbox, nothing worked.

    Any ideas on this one? Possible .NET version mismatch or something.

  17. Pingback: Bring your Project Online data into a data warehouse on-premises

  18. hmendezm

    Hi Guys, thanks for this info. I was able to get info from the SharePOint list but I cannot find a way to update the list in the SSIS package. Someone has a idea of how to update the SharePOint list in the SSIS SQL 2014?

    Best Regards
    H.

  19. Joe Rivera

    Hey, folks!
    I have SP on premise with ADFS, but for the life of me, cannot get it to work (neither using OData component or the SharePoint List Adapter (get a 403 forbidden)). Any advice?

  20. J. B. Rawlings

    All, after leaving my problem alone I came back to it today. I can use Power Query to access our Office 365 SharePoint site but then I would have to upload the data from Excel into the data warehouse. The SharePoint List Source Connector will not work, as far as I can tell with this site. Attempting to use OData method, but I continue to get the following error when setting up the ODate Source Connection Manager: Test connection failed while parsing the XML document because it is not a valid OData service document.

    Does anyone have any insight? I’m apparently able to authenticate and reach the appropriate folder and file. Is it just a mal-formed service doc (I’m not sure who to contact if that’s the case)? Is this doc manually created or automatically maintained as part of the site infrastructure?

  21. Grafton

    I was running into the following error: Unable to load collection for the connection xxx, reason: Cannot open this connection. After much troubleshooting, I figured out that the name of one of my sharepoint lists started with a number, and also contained choice columns. The name of the list in the catalog started with “c_123ListName”, and the choice element started with “C_123ListNameStatus”. Lower case c for the primary list resource, upper case C for the choice resource. Something wasn’t resolving correctly internally. When I changed the list name to start with a letter, the connection worked without issue.

  22. Ken

    I get an error using Odata source.
    Here’s the error message:
    [OData Source [2]] Error: The OData Source was unable to process the data. Object reference not set to an instance of an object.

    SSIS package created using VS 2012 with BI template / table is in SQL 2008.

    Trying to extract data from SP site into SQL table
    Simple an straight forward process.
    oData Souce task> OLEDB destination.
    Any help!!

  23. Mark

    We are trying to connect from SSIS (SQL Server 2012 SP2) to an on prem SP 2010 environment that requires ADFS federated authentication. We’ve tried all options suggested above but without luck, the error is always “An existing connection was forcibly closed by the remote host”.

    We have installed the SP 2010 SDK as well as the v15 and v16 Sharepoint SDKs that Chris mentioned. Any thoughts on how to get this working?

  24. Bhargav

    Thanks a lot for the post. I still cannot connect to the share point site. I downloaded the 64bit SDK that you suggested, but still no luck, getting the same error message? Does it matter where i save and run the SDK from?

  25. Ken Stuber (@KenStuber)

    You just saved my bacon. Thank you!!! The one gotcha I found. If you get an error along the lines of “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 method. (System.Xml)” Check your Site URL. Be sure you’re using the URL of the SITE and not of the LIST (as you would with the old Codeplex connectors).

Leave a Reply