in SharePoint 2010

SharePoint 2010: Business Connectivity Services Walkthrough

Business Connectivity Services (BCS) in SharePoint 2010 provides new ways to connect and integrate with external data in SharePoint. Business Connectivity services also allows users to create external content types, external lists based on the external data. This opens up a new dimension on how you view external data in SharePoint. To get to know more about BCS, you can have a look at the Business Connectivity Services poster – http://bit.ly/bcs_poster

Lets get straight to work!

Here is a simple Customers table that you would like to bring to SharePoint.

image

Ideally you want to create a Customers List in SharePoint which can bring this Customers data into SharePoint and be in sync with the external data.

SharePoint Designer to the Rescue

If you are thinking ‘What, SharePoint Designer? Are you crazy!’ – Well, my dear friend, you are in for a surprise with what SharePoint Designer 2010 can offer you!

The first and foremost thing you would notice is the Office Ribbon integration:

Office Ribbon

No more folder views and is replaced by the Navigation pane:

Navigation Pane 

With SharePoint Designer 2010, creating External Content Types is very simple!

Click on the External Content Types in the navigation pane. This will open the External Content Types tab.

Select New External Content Type from the ribbon.

New External Content Type

This will create the new external content type

External Content Type

Go ahead and change the Name and Display Name to External Customers:

To create external connections and operations, click on click here to discover external data source an…

Now you can add connections:

Connections

SharePoint Designer allows to create external data sources connected to:

1) SQL Server

2) .NET Type

3) WCF Services

image

Lets select SQL Server as our external data source is in the SQL Server

Enter your server details. The Database Name will be Customers.

image

Now, I can see the Customers database and the Customers table that I am looking for:

image

Right click and create the operations. Create All Operations will create the necessary Create, Read, Update, Delete operations.

image

Go through the Wizard and complete it.

image

And here are the operations created:

image

Now, you can create External Lists pretty easily from the Ribbon:

image

Fill in your List details:

image

And our external list is created!

image

No pain, no hassles, pretty simple step-by-step procedure!

Integrating with Office Outlook

To go one step, further, you might actually want to use these Customers in your Outlook so that you can store them as contacts. Integrating external data to Office is just few clicks away with SharePoint Designer!

In the SharePoint Designer, Choose the Office Item Type as Contact for the external content type.

image

This will enable Outlook Contacts integration with the external content type.

Rest is to map the appropriate data source fields with Office properties.

 image

Double click on Read Item and map the fields:

image

Select the appropriate Office field from the Office Property for each of the data source element.

Once mapped, save the changes in SharePoint Designer.

Open the external list in the browser.

Under the List tab in the Ribbon, click on the Connect To Outlook button. This will send the external list information to Outlook. As we have configured the external content type as Contact Office Item, they would appear as Outlook Contacts.

image

A new Outlook add-in will be installed:

image

Below is a screenshot of the external data as Outlook Contacts:

image

Sync to SharePoint Workspace

You can also sync this external list with SharePoint Workspace and take the data offline!

image

Here is a screenshot of the external list in SharePoint Workspace

image

Write a Comment

Comment


7 + nine =

19 Comments

  1. Thank’s for this great intro Chak!

    One question: Is the external list a "real" SharePoint list, with all features. For example can you configure alerts and workflows, to start business actions in SharePoint, if external data changes?

    I just compare this exciting new SharePoint Server 2010 (or SharePoint Foundation 2010 ???) features with the Business Data List Connector (BDLC) found here:

    http://www.layer2.de/en/products/pages/sharepoint-business-data-list-connector.aspx

    Ok, its a much simpler approach. You don’t need SharePoint Designer. Simply enter a connection string, a select statement and primary keys(s) directly in the BDLC custom list settings dialog. That’s it. The list structure is created automatically, you can modify it later on. The SharePoint list is updated by a timer job in background (only changed data).

    Cheers, SharePointFrank

  2. Hi,

    Thanks for the great post, What abou the BI? I havent found much more information abot the Excel and Infopath service in Sharepoint 2010.

  3. Excellent walkthrough! I would like to see some more discussions / community activity around BCS and impersonation with the Secure Store Services. I strongly believe it will have to play a BIG part in just about every BCS scenario. I’ve written the following blog post http://blog.rafelo.com/2010/02/bcs-external-list-error-cannot-connect.html . Which serves somewhat as an introduction; but it’s really just meant to help developers work their way through a specific error.

    Wondering what your thoughts are on the Secure Store Service; best practices, and recommendations.

  4. I followed your article.
    But I get the error: "Access denied by Business Data Connectivity".
    I am the Site Administrator…

  5. on windows 7 SHarepoint 2010 foundation box when i view the list:

    Unable to display this Web Part. To troubleshoot the problem, open this Web page in a Microsoft SharePoint Foundation-compatible HTML editor such as Microsoft SharePoint Designer. If the problem persists, contact your Web server administrator.

    Correlation ID:35c1313f-542c-4853-93bd-636a98ae7e61

    thanks in advance

  6. @Terry – please follow my other post on how to troubleshoot SharePoint errors and find the exact error message from the Correlation ID – /post/Troubleshooting-your-SharePoint-2010-Errors.aspx

  7. Hi, I have an issue. I have created an external list that also inserts new items into SQL server database. In my sql table there are two columns of “datetime” data type that take value in the following format: 2010-08-14 00:00:00.000 when an entry is made directly or data import is done from excel to SQL server table. but when I use SharePoint external list to insert data to SQL server, it makes a wrong entry for the date. eg. If i select 12-Aug-2010 from date time picker, it submits date as following in SQL table: 2010-08-11 18:30:00.000. It always save sdate of one day before the date selected in date picker of infopath form. How can I save the exact date selecetd from external list infopath form to SQL server. ANy help is much appreciated.

    date selecetd in sharepoint external list new item form: 12- Aug- 2010
    Saved in SQL table as: 2010-08-11 18:30:00.000

  8. Chakkaradeep, found your link, while looking for good info on BCS.

    What do you think of BCS as sort of "middleware" between disparate systems? Say, a system with a SQL Server DB and another with an Oracle DB?

  9. @cjkoontz: If you are looking for a good middleware to connect different systems, just evaluare this one: http://www.enterpriseenabler.com/.

    Especially for SharePoint there is the EE for BDLC edition:
    http://www.layer2.de/en/products/Pages/Enterprise-Enabler-for-SharePoint-BDLC.aspx

    A feature comparision of BCS and 3rd party you will find here:
    http://www.layer2.de/en/community/FAQs/BDLC/Pages/Feature-comparision-sharepoint-external-list.aspx

  10. Re: inital post.
    I’m running FS4SP 2010 and want to get an external list of names (i.e. not AD) into SharePoint people search (to make use of phonetic search).
    Got a BCS going to pull the data into an eternal list – mapped theim to Office properties ‘first name’, ‘last name’ etc. – & crawling this list. Searching for names (in the list) works fine in "all sites" search but I can’t work out what crawled properties I need to map to get search going in people search.
    Anyone got any ideas?

  11. I am having difficulty connecting the BDLC to my SQL Server 2008 R2. I am using the .Net Framework Data Provider for SqlServer and my connection string looks like this: “Server=myServerAddress;Database=myDataBase;User Id=myUsername;Password=myPassword;”. I am getting “login failed for user …”. Why? What am I doing wrong to get this error. I have even installed SQL Mgmt tools on our dev server to test my connection and it works fine there. So why does it fail when trying to connect via the BDLC?

  12. I fixed the issue with logging into SQL Server via the BDLC connection string. It was a noob mistake (maybe) … I changed the password for this user and tried the connection again and voila! it works.