Import Data from a SQL Table to SharePoint
In this guide we will show a method to use Data Synchronisation Studio to Import data from a SQL Database to a SharePoint List. You can use this to keep data in SharePoint upto date with data from your SQL Database. This guide assumes you have not saved connections to SQL and SharePoint and so will guide you through the default connections. For detailed connection guides please see our connector pages.
The scenario covered, might be useful if you have data in SQL that you need to share with the business and SharePoint can be used to provide access to this data company wide.
Although we cover using SQL as a source, you could use any of the connectors to import data in to a SharePoint list and keep it updated. For example a few similar to SQL might be:
- Microsoft SQL Server
- MySQL Database
- PostgreSQL Database
- Oracle Database
- CSV File
- XML File
- Excel File
Begin Setup
Before you start you will need the following:
- Windows 10 or Windows Server 2016-2019
- Simego Data Synchronisation Studio
- SQL Database Table, View or SQL
- SharePoint List on SharePoint Online
Connect Source to SQL Table
To start we need to connect the source in Data Sync to your SQL Table. In this example we are going to connect to a Microsoft SQL Server database and import the Northwind demo database Products table to a SharePoint List.
The source can actually be anything where we have a connector, in this example we are using a SQL Table but it could equally be a SQL View, SQL Statement, CSV file etc.
In Data Sync click the Connect DataSource icon on the source to open the Datasource connection window. Then choose the SQL Server (SqlClient) connector under the SQL Database category.
Enter the Server Name choose any credentials in this example we are going to use the current user and then click Connect & Create Library Connection.
By creating a Connection Library connection this allows us to use the same connection for multiple projects to the same database. Later if we need to change the connection configuration we can update the connection library to apply the change to all projects using the same connection.
Now we select the SQL Table to connect to from the SQL object explorer window.
Finally give the connection a name to save it to the library.
The connection is to the database so you only need one connection library entry for each database. Do not create connection library connections for each SQL Table as this creates a messy and confusing library.
If you have already saved the connection to the connection library, simply expand the tree in the connection library window and select the table you want to connect to. You can either drag and drop this onto your source window or right click and select Connect to Source (A).
Once your connected to the SQL Table, Data Sync will load the SQL Table data schema into the source connection and create a default schema map.
Connect Target to SharePoint List
Data Sync can either connect to an existing SharePoint list or you can automatically create a new SharePoint list from the columns in the schema map.
There is a requirement that you must have saved a connection to your SharePoint Site in the Connection Library in order to use this feature. If you have already saved your connection to SharePoint then you can follow the steps below to create a new list.
Create a New SharePoint List
Data Sync has a series of shortcut features, one of which is create a new list in SharePoint. These can all be accessed from the Tools menu.
If you want to create a SharePoint list from your Table Schema first ensure that the Schema Map includes the columns you want to create in SharePoint. The Key column you select here is what defines the unique records typically this will be the table Primary Key.
The SharePoint ID column is read-only and therefore cannot be provided a value from your SQL source. You should therefore not use a column with the name ID as the Key column. If your table Primary Key is called ID then you should manually add a new column say ItemID to the List in SharePoint and map ID to this new column instead.
For each of your String columns in the Schema Map you should ensure that the Length property is set appropriately set any -1 values to an appropriate length for your data. Values above 255 will be created as Note columns in SharePoint.
To create the list open the Tools menu and select Create SharePoint List. This will open the create list form.
Choose your Connection Library Connection from the drop down and give your new list a name in the List Name field. Then choose a column to be used as the Title column in your new SharePoint List, this column will be clickable in SharePoint.
Connect to an Existing SharePoint List
Data Sync can connect to SharePoint Online or an OnPremise SharePoint Document Library. The following details are for a connection to SharePoint Online.
For SharePoint Online you need to connect with Modern Authentication (Oauth2). We have created a default public Azure App that allows you to connect quickly without needing to create your own Azure App. This does not have any data passing through, it simply provides a point of communication from your machine to your SharePoint site to allow authentication.
Alternatively you can create your own app and configure the permissions as required, please see our documentation on SharePoint authentication methods to find out how.
This example will use the default Simego OAuth Application, which uses a pre-registered multi-tenant Azure AD Oauth2 Application to connect to your SharePoint Online instance.
In Data Sync click the Connect DataSource icon on the source to open the Datasource connection window. Then choose the SharePoint Online connector under the Microsoft SharePoint category.
Enter the URL to your SharePoint Site this the URL to the root of your site. Examples of the URL are listed below.
https://company.sharepoint.com/https://company.sharepoint.com/subsitehttps://company.sharepoint.com/subsite/subsite2
Choose the Authentication Method to connect to your SharePoint Site. Choosing the default Simego OAuth Application will use a pre-registered multi-tenant Azure AD Oauth2 Application to connect to your SharePoint Online instance.
Click the Authorise Connection button, this will open your default browser and ask you to sign in to Azure AD and grant access to allow Data Sync to connect to your SharePoint instance.
Once the authorisation is complete go back to Data Sync and choose your SharePoint List from the dropdown list and click Connect & Create Library Connection then give the connection a name and click OK.
Map Source to Target
We now need to configure the schema mapping, This mapping describes the relationship between the source columns and the target columns along with a data type conversion and key column selection.
If you used Data Sync to create the list then this default mapping will be created for you.
The Key column is used to identity each row and therefore must be a unique value typically the table Primary Key. The Data Type is used to allow for basic type conversion, typically this will be the type of the target column. This allows you to apply simple Data Type conversion like converting strings to numbers or boolean to a string etc.
Compare & Synchronise
Next we use Data Sync to load the rows from your SQL Table and the List Items in the SharePoint List and compare these to work out which rows need to be ADDED, UPDATED or DELETED so that the SharePoint List matches your SQL Table.
Delete operations are always disabled by default on new Data Sync projects to enable Delete you need to set the EnableDelete property in the target connection to true.
To start the synchronisation process click the Run Compare button on the main toolbar. This will start the load the rows from SQL and items from SharePoint and display the results to be synchronised. At this point only the data is loaded and compared to create the changeset, no records would have been written.
We can now review the changes that are to be made before running the sync operation to copy the rows from SQL to SharePoint.
Clicking on the 78 Record(s) Only in Source (A) ADD (78) to Target (B) row in the changeset shows us the rows to be ADDED to the SharePoint List. We can the review this and once we happy to continue click the Synchronise button to start the copy process.
Click the Synchronise button and then click Start to begin the actual synchronisation.
![Synchronisation Start]("Synchronisation Start")
This completes the synchronisation, if you then look in SharePoint you will see that your SQL Table as been copied.
You can then go and add or edit records in your SQL Table. When you then compare and synchronise again you will see that only those records which were changed in SQL are copied to SharePoint.
Automate and Schedule
Now that your project is configured you can save it and then schedule it to run via the Data Sync Run Tool, Windows Task Scheduler, or Ouvvi Automation Server.
You may want to automate the project so that it runs on a regular basis, to do this you can use either the Run Tool or Ouvvi.
Ouvvi gives more options for scheduling, with both time and event based triggers. So you could configure a trigger to start your project whenever a change is detected in your SQL table or SharePoint List. Note that this requires using a modified date time stamp in your SQL table.
Alternatively you can use the Run Tool to schedule the project to run using Windows Task Scheduler.
Run via the Run Tool
The Run Tool is an additional program that comes linked to Data Sync and enables you to build out your data integration jobs.
You can use this to group Data Sync projects that need to run in order and add additional step types such as adding an status report to email you when the project has run and if there were any failures. Each step is run in the order it appears in the list and you can apply conditional rules so that the next step can run if the previous one succeeded or had data changes for example. To find out more please see our Run Tool Documentation.
To open your project in the Run Tool, in your Data Sync project open the Tools menu and select Open in Run Tool.
You can then click onto the green Run button to check it runs as expected. If there are data changes you will be provided with a count of how many items were added updated or deleted
Run via the Command Line
Another option is to Run the Run Tool project or single Data Sync project from the command line. To do this pass the path to the project file like this:
Run Tool Project
%DATASYNCINSTALLPATH%\Simego.DataSync.Run.exe -execute "D:\DSProjects\myproject.dsrun"
Data Sync Project
%DATASYNCINSTALLPATH%\Simego.DataSync.Run.exe -execute "D:\DSProjects\myproject.dsprj"
Automate with Windows Task Scheduler
You can then schedule your Run Tool project to run when you need it to using Windows Task Scheduler. For the full details on how to do this see our Task Scheduler Documentation.
Automate with Ouvvi
An alternative option, with more scheduling capabilities is to use Ouvvi Automation Server. Ouvvi enables you to fully schedule and manage all of your Data Integration Projects, from Data Sync projects to SQL Statements to Powershell Scripts.
It provides full logging and documentation capabilities to fully manage your integration operations.
To find out more see our Ouvvi Documentation or send us an email.
Additional Configuration
Use SQL as a Source
You can use your own SQL commands as the source, to do this enter your query into the SQLCommand property on the source connection.
Filter the source SQL Table
You can filter rows on the source by specifying a SQL WHERE statement in the CommandWhere property. Otherwise you can use the filter function available in Data Sync.
Update SharePoint Document Library
If you want to update metadata on a Document Library rather than a SharePoint List. Connect to the Document Library as the target and set the DocumentLibraryUpdateBehaviour to MetadataOnly this way Data Sync doesn't try to upload a new document.