Connecting to SharePoint from SSIS using OData Sou

Question

I am trying to connect to SharePoint hosted on the Microsoft cloud using the OData source. I am trying to pull the Project related data into the sql table from the SharePoint list and process it into the data warehouse.

When I manually login into the SharePoint ,it has got the necessary permissions to read the data and export into excel. But when I connect through the Odata Source in 2012, I encounter the below error. I am using the visual studio Professional 2015 for the SSIS development.

Identity Client Runtime Library (IDCRL) could not look up the realm information for a federated sign-in. (Microsoft.SqlServer.IntegrationServices.ODataConnectionManager)

Has any one of you encountered this issue any time?

Answer 1

I was able to download the list data from sharepoint to SQL using the 32 bit OData source component.The trick is to use the suffix "/_vti_bin/listdata.svc".

For Ex: If your URL for the List data is :https://sharepoint.com/sales the service document location URL should look like https://sharepoint.com/Sales/_vti_bin/listdata.svc. Also Enable the Microsoft Online Services Authentication=True in the Connection manager settings.

Thank you Sharath