Download the trial version of Data Connector and install it to your SharePoint server.

Navigate to the site in which the IT Dept Staff is located.

In the upper right corner, click Settings and then click Site Settings.

On the Site Settings page, click Data Connector Settings under BoostSolutions Software section in the bottom right corner of the page.

On the Data Connector Settings page, click Create Job.

After clicking Create Job, you will enter the Edit Job page.

In the Job Name section, specify a unique name for the job, such as job-1.

In the Direction section, specify the direction for the data connection. Select From list to external data option.

In the List section, specify a SharePoint list or Document Library to which you want to sync data from the external data source. Click to browse and select the IT Dept Staff from the drop-down list.

Because we plan to sync all items of IT Dept Staff list to the external data source, here we select All list items (No filter) option in the Choose a view drop-down list.

In the External Data and Authentication section, configure the settings as following. Specify the external data type and database authentication. (Ensure the user account you specify has the appropriate permissions to access the external data source.)

External Data Type: Select SQL Server as the external data type.

Database Server: Enter the SQL server’s name or its IP address.

Database Authentication: There are two types of authentication, Windows Authentication and SQL Server Authentication.

Windows Authentication: Select this authentication if you want to use the application pool account to access the SQL database. You need to enable Windows Authentication as the Authentication Mode in SQL Server in order to access the database successfully.

SQL Server Authentication: If you select this authentication, you need to enter a SQL user name and password to access the database.

Here we select the SQL Server Authentication.

Username: Enter a SQL user name. The user should have Write permission to the database.

Password: Enter the user’s password.

Click Connect to connect the SQL Server we specified (the dbserver3 in our case). If everything goes right, it will show Connection successful below the Connect button. And the databases existed on the specified SQL server will be populated to the dropdown list as shown in the picture above.

Database: Select a database, here we select BoostSolutionsEnterprise.

In the Data Settings section, specify which external data will be connected to the SharePoint list. Select dbo.IT Staff (the table IT Staff stored in the BoostSolutionsEnterprise database as we described in the sample data section) from the drop-down list as following.

In the Column Mapping section, map the external database fields to the SharePoint list columns as following.

In this article, we plan to map columns between SharePoint list and SQL server database.

By default, the database fields which have the same name and type with that of the columns in the SharePoint list will be automatically mapped.

Because the SharePoint list and SQL database table have different column names, we have to map the columns manually by clicking Add a column mapping.

In Column in List, select Full Name; and in Column in External Data, select Name.

Click Add a column mapping again, in Column in List, select E-mail; and in Column in External Data, select Work Email.

Because we want to fill a value in the Room Number column instead of column mapping, click Overwrite column value and in Column in External Data, select Room Number.

In Column in List, type #35 in the textbox as following. This value will be used to overwrite the target column value.

To remove a mapping, simply click on the icon along the right side of each column.

Select Full Name as the key column. A “key column” is one that uniquely identifies records and can be used to compare data sources.

There are some rules you should follow when configure column mapping. This section will help you smoothly set up column mappings between SharePoint list and external data.

• In SharePoint list, there are some system reserved fields cannot be modified or updated. We define these fields as read-only and cannot be set as target column.

If you set a read-only column as the Target column, there will be an icon next to the column. Move your mouse cursor over this icon, it will show the reasonable message.

• One column can only be mapped to one column, otherwise, there will be an icon next to the column. Move your mouse cursor over this icon, it will show the reasonable message.

Keep other settings as default and click Save to save the settings.

And then you will find that the job has been created on the Data Connector Settings page, the job-1.

Click the Run button for the job-1 and waiting for the job to be run completely.

After the job run completely, click Close.

In the SQL Server database, check the table IT Staff, and you will find that the values in the SharePoint columns have been synced to the target table columns as following.

And the Room Number column is filled with #35 as we configured in the Column Mapping section.