Using the SSIS OData Source Connector with SharePoint Online Authentication

SSIS OData Source Connector with SharePoint Online Authentication
InfoPath alternatives for form designing SharePoint

In this post we will discuss how to resolve the issue which comes while working with SSIS and SharePoint Online. The error looks like below:

SSIS OData Source Connector with SharePoint Online Authentication
SSIS OData Source Connector with SharePoint Online Authentication

Also read:
– Configure On Premise environment for Apps in SharePoint 2016

– Extract all list names from SharePoint 2013 site to csv

– Comparing SharePoint 2016 Boundaries and limitations with SharePoint 2013 and 2010

Solutions:
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.

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.

SSIS OData Source Connector with SharePoint Online Authentication 1
SSIS OData Source Connector with SharePoint Online Authentication 1

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.

SSIS OData Source Connector with SharePoint Online Authentication 2
SSIS OData Source Connector with SharePoint Online Authentication 2

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.”

SSIS OData Source Connector with SharePoint Online Authentication 3
SSIS OData Source Connector with SharePoint Online Authentication 3

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.

SSIS OData Source Connector with SharePoint Online Authentication 4
SSIS OData Source Connector with SharePoint Online Authentication 4

Once you have change option for Microsoft online services Authentication False to True.

Then try to connect again, Click on test connection and see the result.

SSIS OData Source Connector with SharePoint Online Authentication 5
SSIS OData Source Connector with SharePoint Online Authentication 5

Hope this will be helpful.

Similar SharePoint 2013 Tutorials


Leave a Reply