Sharepoint survey to SQL? To set up a backend for PowerBI?

[Virtual_Ames]

Hello Community, 

This is a help wanted note - does anyone know "the best way"/"a good way" to get complex Sharepoint Survey data into SQL? 

Here is the situation.  

I have created a dashboard and report in PowerBI that pulls Sharepoint Survey data.  This is an intricate survey with lots of multi-select options, so the data model is complex - but it works well. 

The next step is to integrate this data with other sources and create time series visualizations - so I am moving my data to SQL (as PowerBI will not scale as needed).

However, getting this data to SQL seems less intuitive than intially thought.  Does anyone have experience pulling Sharepoint List data (think - 50 columns, half of which are record fields which require expanding).  

One indirect option would be to pull the data into Excel, do the transforms there and then connect this to SQL - but there has to be a more direct route?

Someone else must have solved this, or perhaps I am missing some cool approach? 

Suggestions?

Thanks,

Brian 

[v-qiuyu-msft · Accepted solution]

Hi @Virtual_Ames,

According to your description, it seems that you want to import SharePoint list data to SQL database, right?

Based on my research, you can create a SSIS package and use SharePoint List Adapter to retrieve SharePoint list data to SQL Server database. For more information, please refer to this article: Extracting and Loading SharePoint Data in SQL Server Integration Services .

Best Regards,
Qiuyun Yu