AxioWorks Ltd

5 Ways To Export SharePoint Data to SQL Server

[av_textblock size=” font_color=” color=” av-medium-font-size=” av-small-font-size=” av-mini-font-size=” admin_preview_bg=”]

5 Ways To Export SharePoint Data to SQL Server

SharePoint provides us with a great way of capturing and managing data which has seen a huge increase in business data stored in SharePoint On Premise and SharePoint Online lists and libraries. This often means we have the requirement to report against this data. SharePoint doesn’t always make this easy for us!

We can install reporting services into a SharePoint site but it’s still not easy to report against the data. As a consequence of the data not being stored in a relational way, we find there is no easy way to cross-reference data meaning we have to create a dataset for each query and link them using the ‘Lookup’ and ‘LookupSet’ methods. Not ideal!

What would be really nice is if we could export our SharePoint data to normalised SQL Server tables and then use the power of SQL reporting services to generate our reports using this relational model. Reporting Services offers us more flexibility when working with a SQL Server database; We can also use the power and efficiency of the SQL Server database engine to provide to manipulate our data.
There is definitely a compelling reason to investigate how we could export this SharePoint data into SQL Server.
So what approaches could we take to export SharePoint data into SQL Server?

1) Access the SharePoint database directly to export SharePoint Data

PROS:

NONE – This was a trick option! Don’t do this ever!

CONS:

  • Directly accessing the SharePoint content databases is completely unsupported by Microsoft.
  • The schema may well change after SharePoint patches and services packs
  • It can cause performance issues to your SharePoint farm
  • If you get the queries wrong, there’s every chance your reports may also be incorrect!

2) Use CSOM and Powershell scripts to export SharePoint lists

This option provides us with a relational database and we can script the imports. We are able to use the SharePoint Object Model and power shell retrieve the SharePoint lists, interrogate the fields and dynamically create SQL Server Tables before fetching the data, translating it, and loading it into the SQL Server database.

CONS:

  • Slow to setup
  • Lot of work in creating the scripts and tweaking them
  • We need to manage the database schema generation and mappings
  • Complex

3) SQL Server Integration Services (SSIS) and the ‘SharePoint List Adapter’ components for exporting SharePoint data

There’s a good Microsoft resource on what is possible:

http://download.microsoft.com/download/D/2/0/D20E1C5F-72EA-4505-9F26-FEF9550EFD44/SharePoint%20SSIS%20Adapters%202011.docx

CONS:

  • ‘SharePoint List Adapters’ talk to SharePoint using web services, which are slower than using the SharePoint Object Model
  • It’s complicated
  • It takes a lot of effort to set this up
  • Changes to your list schema require changes to your SSIS package and SQL schema
  • Difficult to get working with SharePoint 2013/2016 and SharePoint Online

4) Export to Excel first and then load to SQL Server

You can export the contents of a SharePoint lists, the results of a survey, or document libraries to an Excel spreadsheet.
The export process exports only the columns and rows contained in the list’s current view. If none of the views contain the data that you want to export, then you must create a new view to meet your needs. Alternatively, you can choose one of the existing views, export the list to a spreadsheet, and then delete the unwanted data.

  1. Actions > Export to SpreadSheet.
  2. Save the Excel file.
  3. Open your SQL database with SQL Management Studio.
  4. Right click on your database > Tasks > Import Data.
  5. Select your Excel file as the source, and specific table as destination.
  6. Fill all other needed options.
  7. Click finish.

5) Use AxioWorks SQList to export SharePoint lists and libraries to normalised SQL Server database tables without any development at all

AxioWorks SQList is a lightweight windows service that can be quickly configured to export SharePoint on-premise or SharePoint online data to SQL Server. As well as automatically generating and maintaining SQL schema, SQList will keep your SharePoint data in synch. There are options to archive SharePoint lists and libraries to SQL as well as scheduled exports for those looking for daily exports to a data warehouse or datamart.

Have a look AxioWorks SQList to understand how you can get your SharePoint lists, libraries, files and attachment into normalised sql server databases and continuously replicate them in just a few minutes:

AxioWorks SQList continuously export SharePoint lists and libraries as normalised SQL Server tables, making live SharePoint data available to reporting tools like Power BI, Crystal Reports, or SSRS.

[/av_textblock]

[av_heading tag=’h2′ padding=’10’ heading=’SQList Full Features’ color=’meta-heading’ style=” custom_font=” size=” subheading_active=” subheading_size=’10’ custom_class=” admin_preview_bg=” av-desktop-hide=” av-medium-hide=” av-small-hide=” av-mini-hide=” av-medium-font-size-title=” av-small-font-size-title=” av-mini-font-size-title=” av-medium-font-size=” av-small-font-size=” av-mini-font-size=”][/av_heading]

[av_one_third first min_height=” vertical_alignment=” space=” custom_margin=” margin=’0px’ padding=’0px’ border=” border_color=” radius=’0px’ background_color=” src=” background_position=’top left’ background_repeat=’no-repeat’ animation=” mobile_display=”]
[av_iconlist position=’left’ iconlist_styling=’av-iconlist-small’ custom_title_size=’16’ custom_content_size=” font_color=” custom_title=” custom_content=” color=” custom_bg=” custom_font=” custom_border=”]
[av_iconlist_item title=’Scheduled or adhoc exports‘ link=” linktarget=” linkelement=” icon=’ue812′ font=’entypo-fontello’]
In addition to the SQList synchronisation service it is now possible to run SQList Pro Plus from a command line or via a task scheduler
[/av_iconlist_item]
[av_iconlist_item title=’No list view threshold issues,‘ link=” linktarget=” linkelement=” icon=’ue812′ font=’entypo-fontello’]
SQList uses native SharePoint change tracking capabilities to negates the requirement for columns indexes or to increase the list view threshold.
[/av_iconlist_item]
[av_iconlist_item title=’Expose your SharePoint data to SQL Server Reporting Services (SSRS),‘ link=” linktarget=” linkelement=” icon=’ue812′ font=’entypo-fontello’]
Power BI, IBM Cognos, iDashboard and build powerful SharePoint  ETL solutions
[/av_iconlist_item]
[av_iconlist_item title=’Supports all versions of SharePoint‘ link=” linktarget=” linkelement=” icon=’ue812′ font=’entypo-fontello’]
including SharePoint Online in Office365
[/av_iconlist_item]
[/av_iconlist]
[/av_one_third]

[av_one_third min_height=” vertical_alignment=” space=” custom_margin=” margin=’0px’ padding=’0px’ border=” border_color=” radius=’0px’ background_color=” src=” background_position=’top left’ background_repeat=’no-repeat’ animation=” mobile_display=”]
[av_iconlist position=’left’ iconlist_styling=’av-iconlist-small’ custom_title_size=’16’ custom_content_size=” font_color=” custom_title=” custom_content=” color=” custom_bg=” custom_font=” custom_border=”]
[av_iconlist_item title=’Continuously exports SharePoint lists and document libraries:‘ link=” linktarget=” linkelement=” icon=’ue812′ font=’entypo-fontello’]
SQList will replicate SharePoint lists and documents libraries to single or multiple SQL Server databases.
[/av_iconlist_item]
[av_iconlist_item title=’Dynamically builds a normalised set of SQL tables:‘ link=” linktarget=” linkelement=” icon=’ue812′ font=’entypo-fontello’]
SQList dynamically builds and maitains a normalised set of SQL tables that reflect your SharePoint lists and libraries
[/av_iconlist_item]
[av_iconlist_item title=’ Export sub-sites automatically: ‘ link=” linktarget=” linkelement=” icon=’ue812′ font=’entypo-fontello’]
export lists and libraries from a SharePoint site and its sub-sites in a single replication.
[/av_iconlist_item]
[av_iconlist_item title=’Union view:‘ link=” linktarget=” linkelement=” icon=’ue812′ font=’entypo-fontello’]
SQList can automatically generate an aggregated SQL view of similar lists and libraries across a site structure.
[/av_iconlist_item]
[/av_iconlist]
[/av_one_third]

[av_one_third min_height=” vertical_alignment=” space=” custom_margin=” margin=’0px’ padding=’0px’ border=” border_color=” radius=’0px’ background_color=” src=” background_position=’top left’ background_repeat=’no-repeat’ animation=” mobile_display=”]
[av_iconlist position=’left’ iconlist_styling=’av-iconlist-small’ custom_title_size=’16’ custom_content_size=” font_color=” custom_title=” custom_content=” color=” custom_bg=” custom_font=” custom_border=”]
[av_iconlist_item title=’Quick setup,‘ link=” linktarget=” linkelement=” icon=’ue812′ font=’entypo-fontello’]
with granular selection of lists, libraries, and sites to export.
[/av_iconlist_item]
[av_iconlist_item title=’Can be installed anywhere:‘ link=” linktarget=” linkelement=” icon=’ue812′ font=’entypo-fontello’]
Does not require any changes to SharePoint or SQL Server
[/av_iconlist_item]
[av_iconlist_item title=’Simple licensing:‘ link=” linktarget=” linkelement=” icon=’ue812′ font=’entypo-fontello’]
You only need one license to export data from different SharePoint servers.
[/av_iconlist_item]
[av_iconlist_item title=’Uses standard SQL and HTTP connections:‘ link=” linktarget=” linkelement=” icon=’ue812′ font=’entypo-fontello’]
SQList will connect via local network or across the Internet to cloud hosted SharePoint And SQL instances such as SharePoint Online and SQL Azure
[/av_iconlist_item]
[av_iconlist_item title=’Supports all versions of SQL Server‘ link=” linktarget=” linkelement=” icon=’ue812′ font=’entypo-fontello’]
including SQL Azure
[/av_iconlist_item]
[/av_iconlist]
[/av_one_third]