SQL server connecting to SharePoint server

user_8685726 0
Viewed 3122

I wish to connect SQL server 2012 to SharePoint server. This do complete SQL queries..what is the easy way to do so please?

I want to download data from a SharePoint table into SQL server table basically.

I can only find tutorials connecting SharePoint to SQL server. I want the other way around

Happy to do this via SSIS too..

Please help

sharepoint-2013 etl ssis sharepoint sql-server
2 Answers

There are three options to read from Sharepoint table:

(1) Using ODATA Components

You can use ODATA Source component to access to the Sharepoint Lists

(2) Using Sharepoint List adapter

You can simply use the Sharepoint List adapter components which are an open source project created by SQL Server community within a project called MSSQL SSIS Community:

You can check the project page at the following link:

Or you can simply download the assemblies from the link below:

You can read more about this components in the following link:

(3) Using third party components

user_70312300

If you have Microsoft Access Database Engine 2010 Redistributable installed, you can use OPENROWSET command in a View.

I have created a View using OPENROWSET command. Below is the syntax I used, you need to replace the text enclosed in <> with your values:

SELECT * FROM OPENROWSET('Microsoft.ACE.OLEDB.12.0', 'WSS;IMEX=1;RetrieveIds=Yes;DATABASE=https://<tenant>.sharepoint.com/sites/<sitename>;LIST=<listID>;', 'select <columns list> from <list name as displayed in SharePoint>')

You will need SharePoint List ID that you can get easily by opening the list in browser and going to Settings, and checking the URL.. it is in format: {247c5fd8-32cd-4536-b1d0-e2e62845f174}. Please check this link.

user_59421730
Related