The fast, easy and complete integration of external data, e.g. SQL databases, files or other application data, is a common requirement in the use of Microsoft SharePoint technology. Solutions, such as the Business Data Catalog (BDC) for SharePoint 2007 or the Business Connectivity Services (BCS) for SharePoint 2010, are often complex at the facility, require additional tools or programming, have limitations or are available for certain SharePoint editions only. The Business Data List Connector completely solves those issues.

The Business Data List Connector for SharePoint 2007 and 2010 offers a SharePoint "External List" by connecting 100% NATIVE SharePoint lists (no SharePoint Web Parts, no BCS) to almost any external business data source in just minutes directly in the list settings, using a very simple browser-based dialogue. The desired data can be populated to the list and presented without any restrictions, with the full functionality of common native SharePoint lists made available:

• No additional tools or programming required. The connection can be configured easily directly in list configuration with a few settings only (see screens below). Every SharePoint admin can do this in minutes.
• Data updates automatically in background (timer job) or on-demand (Action Menu / Ribbon Button).
• Changed data transferred only. Optional bi-directional connection: Write-back changes made in SharePoint to external data sources automatically with full CRUD (Create / Update / Delete).
• All existing ODBC, OLEDB, and .NET based ADO.NET drivers are supported: You can connect to SQL Server, SQL Azure, MySQL, Exchange, Active Directory, SharePoint, MS Office Excel, Access or CSV text files, XML, EDI, Oracle, IBM DB2, Informix, AS400, Cache, ERP/CRM systems e.g. MS Dynamics CRM / NAV / AX, SAP, Salesforce.com, Twitter, Google, Facebook, QuickBooks, Peachtree, HL7, Virtual Store, general Web Services, .NET or COM+ objects and many others.

• Known BCS issues and limitations are completely solved - all list features available now: Views, sorting and grouping, filters, calculated fields, search, enterprise metadata, lookups can be created as usual.

• All list types are supported: Contacts, tasks, events, custom lists etc. You can take the external data offline via Outlook and / or Workspace.

• You can add additional SharePoint-only columns or attachments to the the external data: Documents, comments, ratings, tagging or other Web 2.0 content.
• List workflows (SPD-, VS- and Nintex-based) and notifications per RSS or email can be used to take business actions in SharePoint, if external business data records are changed.
• Easy SharePoint migration from 2007 to 2010: All settings are kept working. Free upgrade for all customers with Software Assurance (SA).
• The BDLC additionally makes available any external data source for enterprise metadata tagging to use with the new SharePoint 2010 Knowledge Management and Social Networking features without any restrictions.
• Add-ons available: Use the “Key Value Lookup Column” to keep the foreign key relationships between SQL data source tables for SharePoint list lookups.

Please read here, what customers think about pros and cons of this product. 

Fig: To see the SharePoint BDLC presentation full-screen please click this image. In case of technical issues using Slideshare just download the presentation here.

The SharePoint Business Data List Connector is available for Microsoft SharePoint Server 2010 and Windows SharePoint Servicves 4.0 / Microsoft SharePoint Foundation. Microsoft Office SharePoint Server 2007 (MOSS, all editions) and the free Windows SharePoint Services 3.0 (WSS) is also supported.

To take your data to the SharePoint Cloud (e.g. SharePoint Online, Office 365 or any other hosted sandbox) please evaluate the Cloud Connector for SharePoint.

Whether you are an Systems Integrator or Service Provider, with the BDLC you can dramatically reduce costs, improve business process productivity, and rapidly create the "agile" enterprise for you and your clients.

• Beat the competition with lower integration cost
• Complete your integration project in less time
• Bill a little bit more - but program much less
• 30% discount as a reseller or implementer

• Features and Benefits
• Free Registration and Download
• How to buy this product
• Product description
• FAQs (below on this page)

The SharePoint Business Data List Connector (BDLC) provides the complete read-only or optionally write-back (CRUD) features of any native SharePoint 2007/2010 list for almost any external LOB data sources in 3 simple steps:

• Create a native list of any type to host the data and simply click "Connect to external data source" in the list settings dialog.
• In the BDLC settings dialog choose from the installed connection providers and enter the appropriate connection string (e.g. OLEDB, ODBC or custom) and validate it.
• To select the desired data, please enter a select statement as supported by your external data source and validate it. Check the result in preview mode and select primary key(s), to make use of advanced features. So you can enable list workflow or change notifications per RSS or email to take business actions, if your external LOB data records are changed.
• Create the SharePoint list structure by adding additional columns, check the field / column mapping and update the data using the command buttons with one click. If a primary key is available, only modified data is updated in the list with high performance (background batch update via timer job).
• You can optionally activate write-back to data source (CRUD: Create, Update, Delete features), if supported by the used driver.
• You can additionally use Enterprise Enabler for SharePoint Business Data List Connector to connect SharePoint lists to complex bi-directional aggregations, transformations and manipulatiosn of entities among multiple Line Of Business applications, e.g. Web Services, .NET and COM objects, SAP etc.

Fig.: Sample usage scenario of the SharePoint Business Data List Connector to expose the SQL Server categories table as native SharePoint list. No other settings required to connect.


Fig. With BDLC SharePoint lists can be connected directly to almost any external data source. Write-Back is supported for most databases via .NET Provider or OLEDB.

Please register using the top left form to download the SharePoint Business Data List Connector (BDLC) free shareware edition today.

BDLC solves a lot of business requirements with SharePoint external data integration:

• You want to access any external data source automatically up-to-date without any programming as native SharePoint list of any type.
• You want to have the full functionality and features of a usual native SharePoint list, e.g. personal views, filter, grouping, calculated columns, custom columns, etc. for external data as well.
• You have to pull contacts from your CRM system to a native SharePoint contact list, e.g. for offline synchronization to Outlook – possibly with write-back.
• You have to pull tasks from your Help Desk, Action Request System or Exchange Server to a native SharePoint tasks list, possibly bidirectional.
• You have to expose date- or time-based information from external systems in a native SharePoint calendar with different views.
• You want to provide typical master data of your company (e.g. countries, cities, departments) centrally for consistent use in portal-wide SharePoint lookups without any manual duplication. You can establish native SharePoint lookups to these lists or you can use our Key Value Lookup Column Add-On to manage native foreign key relationships with lookup columns as usual with databases for SharePoint 2010.
• You want to save license costs for external systems while exposing the data within the well-known SharePoint list user interface so that everybody that has access rights could easily work with it.
• You want to subscribe to native SharePoint RSS newsfeeds or email-based notifications to get a message, in case of external data records are changed.
• You want to start business workflows (based on SharePoint Designer, Visio, Visual Studio or Nintex) in SharePoint in case of external data changes without any restrictions.
• You have to add additional information like comment columns or attachments to external LOB data in SharePoint.
• You want to take any LOB data offline - display only or with with full replication unsing BDLC connected lists with SharePoint 2010 (Outlook and/or Workspace).
• You want to make use of the new SharePoint 2010 Knowledge Management to tag external data completely automatically in background with managed metadata or enterprise keywords for improved navigation and findability.
• You want to comprise external data seamlessly into the SharePoint 2010 Social Networking, e.g. for ranking, rating, comments or activity feeds on mySite.
• You want to make external data available for any kind of SharePoint search, including WSS.
• You want to use charts and diagrams based on external data, but won’t directly access the data sources in real-time.
• You want to use Key Performance Indicators (KPI) lists with external data pulled from lists.
• You won’t use Web Parts to display external data, because there are restrictions in functionality, security and performance compared to lists
• You want to import Excel data on a daily base.
• You want to present data aggregations or pivot queries in a Business Intelligence (BI) scenario with full list features.
• You want to join data from different tables or data sources and provide as native SharePoint list.
• You want to display data from Microsoft Project.
• You have to pull data from Active Directory, e.g. persons or devices.
• You want to fully integrate Microsoft Exchange or Outlook data, e.g. contacts, email messages or public folders.
• You have to connect two SharePoint lists, e.g. between site collections or installations.
• You want to pull data from the file system via Index Server into SharePoint lists.
• You have Microsoft Office SharePoint Server 2007, but no Enterprise Version. No Business Data Catalog (BDC) is available.
• You have to use the free Windows SharePoint Services (WSS 3.0) and no Business Data Catalog (BDC) is available.
• The Business Data Catalog is too complex for a given requirement and you won’t use additional configuration tools.
• You have to automate regular imports of external data.
• You have to connect complex or not connected external systems, e.g. with export to CSV or fixed length text files on one side and automatic update of SharePoint lists on the other side.
• You have to provide data integration solutions that work in the same way for SharePoint 2007 and 2010 and are easy to migrate.
• You want to write-back changes made in SharePoint lists to external data sources with full Create, Update and Delete (CRUD) codeless.
• You want to configure lots connections to external data sources programmatically per API.

Please contact sales@layer2.de , to find a solution for your specific scenario. 

INSTALLATION

The installation is done using a SharePoint Solution Package. The included wizard distributes all files to a SharePoint farm, if required, and does all necessary configurations. Before installation please make sure,

• that all servers are available online
• backup your current installation approve software on evaluation systems bevor installing in productive system
• use a regular service interval to install the solution The application will not be available for a short time, because the application pool is restarted (IISreset).

For WSS 3.0 / MOSS 2007 go to the 2007 folder of the distribution. For SharePoint Server or Foundation 2010 goto the 2010 folder. Then start the installation process by executing the file "Install.Layer2.BusinessDataListConnector.exe" from a local directory on the server, that hosts the SharePoint Central Administration with appropriate access rights (run "As Adminstrator" on Windows Server 2008).

Fig.: Starting the installation process

First the considerations for the installation are being checked.

Fig.: Checking of considerations for the installation process

If there are any problems, please take a look at our FAQs (General FAQ, Installation).
As a next step you have to accept the EULA.

Fig.: Accepting the license agreement

After successful installation please select the desired web applications for deployment of the new feature.

After successful deployment there is a new site collection feature available in all site collections of the selected web applications. To use it, the new feature „Business Data List Connector“ has to be activated on Site Collection level by a Site Collection Administrator. That has to be done in Web Site Settings > Site Collection Administration under Site Collection Features. After activation the feature can be used.

Fig.: Activation of the feature "Layer2 - Business data List Connector" on site collection

 

After successful activation on site collection level the new feature is available in the List Settings dialog: "General Settings > Connect to external data source".

 

USAGE OF THE BUSINESS DATA LIST CONNECTOR

 

In order to connect an existing SharePoint list with external business data, you  have to  choose "Connect to External Data Source" in the General Settings section of this list. 



Fig.: Starting with V2.0 you can connect any type of SharePoint list directly to external data.

 

If you don't use an existing list you have to create a new one of appropiate type (e.g. contacts, tasks, etc. or simply custom list) and give a name and description to the newly created list as usual, e.g. MyBDLC in this sample.

 

 

Fig.: Enter name and description of the newly created list

 

To connect this list with your external business data, goto list settings  and simply choose Connect to external data source to enter your connection string, select statement and primary key(s).

 

 

 

 

 

BUSINESS DATA LIST CONNECTOR SETTINGS

 

In the settings dialogue please go the following steps to configure your list connection.

 

 

Fig.: Business Data List Connector Settings dialogue

 

 

STEP 1 - Connect the external data source using a connection provider and connection string

 

First choose your connection provider. If installed, choose the native .NET database provider, e.g. for SQL Server or Oracle. Choose OLEDB if no native provider is available. Choose ODBC if no OLEDB provider is available. 

Enter your connection string to your external data source and validate it. Connection strings are available for virtually any external data sources, e.g. databases like Microsoft SQL Server, Oracle, MySQL, Informix, DB2 etc, Microsoft Office data like Excel, Access or even text data files. Btw. connection strings are available for external SharePoint lists too...
See FAQs for samples and descriptions.

 

Fig.: Step 1: Enter the connection string (OLEDB sample here) and validate it

 

Fig.: The BDLC connect SharePoint lists to almost any external data sources

More detailed information about connection strings you'll find in our FAQs. 

 

 

STEP 2 - Select the desired data using a select statement

 

Enter your select statement supported by your external data source and validate it. Select statements are available for virtually any external data sources, e.g. databases like Microsoft SQL Server, Oracle, MySQL, Informix, DB2 eetc, Microsoft Office data like Excel, Acccess or even text data files. Btw. select statements are available for external SharePoint lists too. For most data sources you can start with e.g. "Select * FROM Tablename" where tablename should be a valid name of a table or view in your data source.

 

 

Fig.: Step 2: Enter select statement and validate it.

 

 

Fig.: Preview your result set to verify your select statement. You can paste the given field names, if required for primary keys.

 

More detailed information about select statements you'll find in our FAQs.

 

 

STEP 3 - Optionally enter primary key(s) to use advanced update features

 

In most cases your external data would have one or more primary key(s). A primary key of a relational table uniquely identifies each record in the table. It can either be a normal attribute that is guaranteed to be unique (such as Social Security Number in a table with no more than one record per person) or it can be generated by the DBMS (such as a globally unique identifier, or GUID, in Microsoft SQL Server). Primary keys may consist of a single attribute or multiple attributes in combination.
If a primary key is available, then advanced update features can be used. For more information about possible primary keys / field names please take a look into your result set using the Preview button.

 

 

Fig.: Step 3: Optionally enter primary key(s).

 

 

Remarks:

 

The validation function only validates, wether the entered field name(s) exists in the result set or not. If invalid primary keys are given, the resulting SharePoint list data could become invalid too.

 

If no primary key is given, with any update the whole result set of the given select statement is re-inserted again. That can cause performance problems and change notifications per RSS or email will not work as expected.

 

If valid primary key(s) are given, modified data will be updated only - very fast in background. Change notifications and workflows can be used in this case, to take business actions, if external data records are changed.

 

STEP 4 - Create your SharePoint list structure and update the data items

 

To create the list structure required to display your external business data, please press "Add Columns" in SharePoint section of the settings dialogue. The fields of the result set of the given select statement are added and mapped to common SharePoint list fields automatically. You can create, rename, modify or delete the column fields afterwards manually. Also you can modify the field types, if required (e.g. to longer text). The mapping of external fields to SharePoint columns is done by internal column names. See the mapping dialogue for help.

 

 

 

More about field mapping, supported field types etc. see FAQs.

 

 

To update the data in your SharePoint list with your external business data, please press "Update" in the SharePoint section of the settings dialogue. As mentioned above, if valid primary key(s) are given, modified data is updated only.
Warning: Updating a SharePoint list, that is connected to an external data source, can modify its content.

 

STEP 5 - Enable background updates optionally or update using the Action Menu

 

In this section you can optionally enable background updates. The updates are done automatically by a timer job, one time per hour by default. You can modify this interval later on in the "BDLC Configuration List", a list on the root site of your site collection.

 

 

Fig.: The timer job enables fast background updates of your data items.

 

For more information, how to change the timer job options, please see FAQs.

Possibly it is a better approach to update the list manually using the additional action menu or ribbon button .



Fig.: Update a connected SharePoint list manually using the Action Menu.

In any case of update you have to check the access rights to external data source and to the current SharePoint list.

 

RESTRICTIONS OF THE DEMO VERSION

 

In the evaluation version the number of BDLC lists per site collection is limited to 3 and the number of managed entries in the BDLC list is restricted to 25 items. 

 

LANGUAGES

 

The feature is currently available in German and English language. If used in other language context, the English language is applied by default. Requests for other languages please send to sales@layer2.de.

UNINSTALL

To completely uninstall the solution, go these steps to the other direction. Please first delete all lists of type Business Data List Connector. Otherwise lists of these Type become inaccessible, after the feature is removed. Then deactivate the feature „Layer2 Business Data List Connector“ on site collection level. Thereafter the configuration list of the BDLC has to be removed. To do that, delete the list “BDLC Configuration List” in the root site of every site collection where the feature has been activated.

Note: You can delete the "BDLC Configuration List" of a given Site Collection only if the BDLC feature is deactivated for this Site Collection.

Then start the installation wizard and choose "Remove". All installed files will be removed.

 

Fig.: Remove the Business Data List Connector Feature