Overview

This PowerShell script will connect to one or more specified SharePoint sites, find all the SharePoint lists in those sites and export the contents of the sites to a table in an SQL database. Whilst Power BI can be used to report directly on the SharePoint list contents, SQL is a far more flexible tool for rapidly obtaining results for user-defined queries.

Where to Find It

Name of script: ExportSharepointListsToSQL.ps1

How It Works

The script connects to two SharePoint sites: the BurySpeakeasy-SLTManchester site and the BurySpeakeasy site. The BurySpeakeasy-SLTManchester contains most of the commonly used SharePoint lists, including (but not restricted to):

• Speakeasy Members
• Staff/Volunteers
• Carers
• SLT Contacts
• Meetings
• Meeting Attendees

The BurySpeakeasy site provides access to just the Asset Manager Sharepoint list.

Each list is read by the script and a SQL table is created in the Speakeasy database, which is located in Azure. See Speakeasy SQL Databases for more information on SQL database configurations. Each SQL table is deleted and recreated each time the script is executed. Each SharePoint list entry is then inserted into a SQL table as a separate SQL record.

To enforce referential integrity, foreign key relationships are added to the database by invoking a built-in stored procedure called dbo.AddAllForeignKeyConstraints. This procedure should be modified to add extra relationships as appropriate, e.g. if new lists are added to Sharepoint. Similarly, prior to deleting the SQL tables, all foreign key relationships are deleted by invoking the built-in stored procedure dbo.DeleteAllForeignKeyConstraints. This procedure does not need modifying as it uses a query to determine all the foreign key constraints and will therefore detect and detect any newly-added relationships. The SQL for each of these stored procedures can be found in the git repository SpeakeasyScripts Repository