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. The BDLC comes with it's own web services based driver, the Layer2 SharePoint List Provider, that supports SharePoint-to-SharePoint connections out-of-the-box.

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.

2. Enter connection string as follows:

Web- and View-GUID based:

WebSiteUrl=https://myserver/;List=ListName;ViewId={ViewId};Login=domain\name;Password=pwd

or URL-based:

Url=http://myserver:80/Lists/mysourcelist/AllItems.aspx;Login=domain\name;Password=pwd

or with current credentials:

Url=http://myserver/Lists/mylist/AllItems.aspx

with parameters:

Url=http://myserver/Lists/mylist/AllItems.aspx?View={7c989618-db65-46ac-9c3b-bd33c222883a}&SortField=Testcol1&SortDir=Descc

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.

8. Write-back

Write-back of changes in the destination list to the source list is not supported yet for SharePoint-to-SharePoint connections in the current version.

faq bdlc sharepoint 2007 integration

Fig.: Setup of a SharePoint-to-SharePoint list connection in the list settings dialog with BDLC.

Please note:

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.

Known Issues:

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.

Layer2 Data Provider for SharePoint (CSOM): Use Cases

Retrieve data from another SharePoint list or library (metadata only, no files), e.g. same or other site collection, other SharePoint installation (including externally hosted) and keep it up-to-date automatically in background.
Write item changes (insert / update / delete) to another list, e.g. to sync Office 365 / SharePoint Online with local installation.
Sync local calendars, tasks, contacts etc. with Office 365, e.g. for mobile access outside company.

Known issues

Calling a webservice over SSL in SharePoint (e.g. Office 365 via CSOM) could raise "Could not establish trust relationship for the SSL/TLS secure channel..." error message.

Since Microsoft SharePoint uses its own certificate store, it does not trust the global standard certificates. To make your SharePoint trust your certificates, you have to add them to your trusted certificates in SharePoint Central Administration.

That is also an issue with the certificates that Microsoft uses on their Office 365 Login Page and SharePoint Online sites. To make your SharePoint trust these certificates, you have to add them to your trusted certificates in SharePoint Central Administration.

READY TO GO NEXT STEPS?

Register for free download.

Keep your SharePoint in sync. Download and try today.

Questions? Contact us.

We are here to help. Contact us and our consulting will be happy to answer your questions.