SharePoint Data Connector is a lightweight out-of-the-box solution that provides a secure and convenient way to transfer business data between SharePoint lists and external data sources. Using SharePoint Data Connector, the data can go both ways, export SharePoint list content to an External database or import your business data to a SharePoint list from multiple External data sources.
Go to Site Actions -> Site Settings, and then click Data Connector Settings under BoostSolutions Software.
Or enter List Settings page from a list or library, and click Data Connector Settings under General Settings.
To create a job, click Create Job in the Data Connector Job Center page.
Type a unique name for this job.
Select a direction for data connection in the dropdown list.
Specify a list or library which you want to connect to external data. The settings of the List may vary according to the direction.
• If Direction is From list to external data
When the direction is set as From list to external data, then the list is the data source, and external data is the target.
To configure a list:
1. In the Select a list dropdown list, choose a list or library as the data source.
2. In the Choose a view dropdown list, select a view to filter items.
If you select All list items (No filter), it means that all items of list will be exported to external data source.
• If Direction is From external data to list
When the direction is set as From external data to list, then the list is the target, and external data is the source.
You can choose an existing list or create a new list as the target for data connection.
Select one of the options:
Choose an existing list: Select a list as the target list in the site tree.
Create a new list: Create a new list as the target list. The list type will be created as Customized List.
Data Connector supports multiple popular external data systems, such as Microsoft SQL Server 2000-2008 R2, Oracle Database and My SQL 5.0 Database, SharePoint 2007/2010/2013.
SharePoint - SQL
Note: For SQL Server, Windows Authentication will use the application pool account as the connection account.
SharePoint - Oracle
SharePoint - MySQL
SharePoint - SharePoint 2007/2010/2013
• If the direction is From list to external data, you need to choose a database table to receive the data.
• If the direction is From external data to list, you can specify the connected data by a table, view or a SQL clause.
If the SharePoint 2007/2010/2013 is chosen as the external data, the Data Settings section will not display.
Note: Personal views will not appear in the drop-down menu.
A column mapping allows users to accurately map the external data fields to a SharePoint list. Also, you can specify key columns to uniquely identify records/items and to compare the data between SharePoint list and external data. You have to specify at least one key column.
Add a column mapping: Map a list column to external field.
Overwrite column value: Overwrite the original target column value.
If you specify the direction as From external data to list, and select Create a new list in the List section, then the external fields will be loaded in Column Mapping section, and you can choose which fields will be imported to the list.
You can select what operations to do for the target by enabling/disabling the three options. The Write-back feature will add, update or delete changes from SharePoint list to external data source.
When Background Update is enabled, Data Connector will run the Data Connector job based on your plan.
(1) Send notification emails to the specified email address when errors occurred.
Note: Email notification is available only when Background Update is enabled.
(2) Sample notification email:
(3) Enable logging for each Data Connector job.
(4) Log format:
Make sure to save the job before running by clicking the Save button, thus it can be used later.
After a job is created in the Data Connector Job Center, you can click to run a job manually. Then you can see the following dialog box, it will show the progress and the result of the running job.