How to Connect Data between External Data and SharePoint Lists

As a database administrator, I manage a table named “Employee_Info” in database1 on our SQL Server. Meanwhile, my colleague Tom, a department manager, manages a SharePoint list named “brain-employee.” Now, we want to create a connection between the two different data sources and merge the data. Additionally, I want to customize the data connection profile to protect some sensitive data, like Salary for example. However, how do I do this?

Luckily, I found a simple and useful tool called SharePoint Data Connector, which provides a secure and convenient way to transfer and manage business data between SharePoint lists and external data sources.

Now, let’s see how it works:

1. First of all, create a profile on the Settings Page.

2. Then, specify the SharePoint list and the connection direction (List -> Data or vice versa).

3. Connect the external data sources (I selected the list “brain-employee,” the direction – “From list to external data,” connect to SQL Server –database1 – “Employee_Info” table.

4. Enter your conditions, including filter settings using SQL statements and column mapping (My SQL statements is —where Depart = ‘brain’ to filter brain department data. Setting these values to columns, except that sensitive column data “Salary“).

5. We have now completed our profile settings.

6. Now, click the “Run Now” button to connect the data.

7. If you don’t want to delete records that only exist in the target, set “Run Options” function, which provides three options: Enable ADD, Enable UPDATE, and Enable DELETE.

8. I don’t want to delete any data, so I unchecked the Enable DELETE option.

9. If you want to run the profile in the background, enable Background update.

10. Specify an email address to send notification emails to for each error that occurs while background update is enabled.

11. Additionally, logging can be enabled in the same field.

This is easy, right?

Although this is only the initial release of Data Connector version 1.0, it can support SQL Server, My SQL and Oracle databases very well. Look forward to the next build to be compatible with many more data sources!