How to create a SharePoint-to-SharePoint list connection with BDLC?
The Business Data List Connector for SharePoint can
connect almost any data sources directly with native SharePoint lists via ODBC,
OLEDB or .NET based providers. Because auf driver issues with the Microsoft
OLEDB driver for SharePoint there were difficulties to connect SharePoint lists
to SharePoint lists, e.g. on another installation. Now BDLC 3.4 comes with it's
own web services based driver, the Layer2 SharePoint List
Provider, that supports SharePoint-to-SharePoint connections
Please note: This provider is for use with SharePoint 2007 only. For
SharePoint 2010 or higher please use this one.
You can connect a SharePoint list
with another SharePoint list (at the same or at another installation) to pull
the data and keep up-to-date with background update as follows:
1. Select the Layer2 SharePoint List Provider as driver.
It is automatically provided with the BDLC installation. It is required only
at the destination server. The source system is connected via web services.
SharePoint 2007 is supported as well as 2010.
2. Enter connection string as follows:
- Web- and View-GUID based:
- or with current credentials:
- Url or WebSiteUrl specifies the adress of the SharePoint
source web site and can contain the complete path to the list.
- List optionally specifies the name or ID (GUID) of the view
in the source list. Guids must not be decoded.
- Login specifies the user login, optionally including
domain. If not present, the credentials of the currently logged in user are
used. Please take cate in case of background updates (see below).
- Password specifies the user password.
3. SQL queries
SQL queries are not supported as this time. Create views in the source system
to filter data. Use calculated fields in the source systems to simulate
"aliases" for field names in SQL queries to fix mapping issues.
4. Primary key
Set ID as primary key (as pre-filled).
5. Add columns
The columns of the source list are created automatically in the destination
list, if not already available.
6. Check Mapping
Columns are mapped automatically by internal column name.
7. Background Update
If background update is planned, please enter user login and password
or make sure, that the service account for the timer job has the rights to read
your source list.
Write-back of changes in the destination list to the source list is not
supported yet for SharePoint-to-SharePoint connections in the current
Fig.: Setup of a
SharePoint-to-SharePoint list connection in the list settings dialog with
- Column types, names and settings of the source list are used to create
the structure of the destination list.
- Columns of type managed Metadata, Lookup, User/Group are not
fully supported and can be transferred as text-only. You can delete the
auto-created columns and replace by the required column type. Please note, that
the result of transferred lookup and user / group column content can be
undefined, if the context (lookup list, user, group) is not available or has
different IDs within the target system.
- Calculated columns are supported as data source.
- Column restrictions, descriptions, formatting, default
settings, length etc. are taken from source list
- There is an issue with the preview display on SharePoint 2007 list
data source. Additional internal columns are shown.
- Be careful with with special characters in internal field names of the
source list. Sometimes there are issues with the SharePoint web service to
retrieve data for those type of columns.
Please take care about the primary key:
When selecting the Layer2 SharePoint Provider, ID is set as
primary key automatically and the column "bdlcID" is created in the destination
list and mapped to host the source ID values.
- Error Message: "List structure could not be created. A column
named 'Title' already belongs to this DataTable." Workaround: If you are
referencing a view in your connection string, do not include the title field in
that view. The connector maps the title field by default, and attempting to add
a second mapping via the view appears to be the cause of this error. That is an
issue with the provider and will be changed with the next version.