Using the SSIS OData Source Connector with SharePoint Online Authentication

SSIS OData Source Connector with SharePoint Online Authentication

Tags: ssis odata source connector sharepoint, odata source for sql server 2014, ssis odata connector, ssis odata sharepoint, ssis sharepoint list adapter sql 2016, ssis sharepoint list adapter sql 2014, ssis sharepoint list adapter sql 2012, ssis 2016 sharepoint list, ssis sharepoint connection manager, odata source for sql server 2014, ssis odata connector, ssis sharepoint list adapter sql 2016m ssis odata destination, odata source for sql server 2016, ssis sharepoint online list adapters, ssis sharepoint connection manager


In this post, we will discuss how to resolve the issue which comes while working with SSIS and SharePoint online (Download PDF for FREE). The error looks like below:

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

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 sharepoint list adapter sql 2016
ssis sharepoint list adapter sql 2016

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 sharepoint list adapter sql 2014
ssis sharepoint list adapter sql 2014

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 2016 sharepoint list
ssis 2016 sharepoint list

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 sharepoint connection manager
ssis sharepoint connection manager

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

Read some SharePoint tutorials

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

ssis sharepoint list adapter sql 2016
ssis sharepoint list adapter sql 2016


Hope this will be helpful.

Check out Best Alternative to InfoPath -> Try Now

You May Also like the Following SharePoint Online Tutorials: