Source
If you wish to;
- Import / Migrate OleDb data to a SharePoint list
- Import / Migrate documents to SharePoint where the meta data is accessible via OleDb
- Bulk create items in SharePoint where the meta data is in OleDB e.g.
- Import / Migrate web pages to SharePoint where the meta data is accessible via OleDb
Then you can specify an OleDb table data source.
Configuration
OleDB Table
You can define a data source as being a table using OLEDbTable. This also (optionally) allows each row to be updated after import as successful or otherwise along with any Exception data. This is useful for large imports.
Below the table that we are telling Import for SharePoint to use is a worksheet “Clients” in an excel spreadsheet.
|
<Source> <SourceDataSetType>OLEDbTable</SourceDataSetType> <OleDbSourceDataSetSettings> <ConnectionString>Provider=Microsoft.ACE.OLEDB.12.0;Data Source=\\vmware-host\Shared Folders\projects\ClientFolders.xlsx;Extended Properties="Excel 12.0 Xml;HDR=YES;IMEX=0";</ConnectionString> </OleDbSourceDataSetSettings> <OleDbTableSourceDataSetSettings> <TableName>clients$</TableName> </OleDbTableSourceDataSetSettings> </Source> |
If you want to update the source table with status data.
The table must contain columns ImportKey, ImportStatus, Exception.
ImportKey must be unique.
In the example below only the first 10 rows will be considered for import since only they have the importstatus “Import”.

OleDB Select
You can define a data source as simply the results of a select statement using OLEDbSelect.
|
<Source> <!-- The source used is OLEDbSelect meaning that Import for SharePoint expected to run a SQL select statement against an OleDB data source. OleDB select will ignore columns like importstatus--> <SourceDataSetType>OLEDbSelect</SourceDataSetType> <!-- The Connection string uses a microsoft provider to access an Excel spreadsheet--> <OleDbSourceDataSetSettings> <ConnectionString>Provider=Microsoft.ACE.OLEDB.12.0;Data Source=\\vmware-host\Shared Folders\Projects\ProductDevelopment\ImportForSharePoint\WorkingArea\Examples\JobDescriptions.xlsx;Extended Properties="Excel 12.0 Xml;HDR=YES;IMEX=0";</ConnectionString> </OleDbSourceDataSetSettings> <OleDbTableSourceDataSetSettings /> <!-- The select statement will get all the jobs from the jobs worksheet --> <OleDbSelectSourceDataSetSettings> <SelectStatement>select * from [jobs$]</SelectStatement> </OleDbSelectSourceDataSetSettings> </Source> |
Further Thoughts
The database behind OleDB source could be;
- SQL Server
- Access
- Excel
- Oracle
- Simba
- Interbase
- Firebird
- PostgreSQL
- MySQL
- MariaDB
- SAP ERP
- MongoDB
- Azure Table