We’re excited to announce that Penelope Coventry’s Microsoft SharePoint Designer 2010 Step by Step (ISBN 9780735627338; 592 pages) is now available for purchase!
You can find the book’s introduction in this previous post.
In today’s post, please enjoy an excerpt from Chapter 7, “Using Business Connectivity Services,” which describes what BCS is. We also include an exercise that shows you how to create an Office application external content type (ECT).
Microsoft Office SharePoint Server 2007 introduced functionality that allows companies to present data from backend applications on Web pages and as column values in lists and libraries. The Microsoft Business Connectivity Services (BCS), originally called the Business Data Catalog, was one component of this functionality and is now available in both Microsoft SharePoint Foundation 2010 and Microsoft SharePoint Server 2010. The definitions that allow BCS to connect to backend applications can also be used to reveal external data in Microsoft Office 2010 applications, including Microsoft Outlook 2010, Microsoft Access 2010, Microsoft SharePoint Workspace 2010, Microsoft Word 2010, Microsoft InfoPath 2010, and Microsoft Excel 2010. Microsoft SharePoint Designer 2010 is one of the main tools you use to create these BCS connection definitions, and you can also use it to create views and data entry forms with the Data Form Web Part (DFWP).
In this chapter you will learn what BCS is. You learn how to define external data sources and how to create external content types from those data sources. You also learn how to create an external content type that can work with Office 2010 applications. Using these external content types, you will learn how to create an external list and how to export and use the BDC model so that you can import the model into other SharePoint environments or use it with Office applications. Readers who have the Enterprise edition of SharePoint Server 2010 will learn how to create profile pages and use associations, which can be used with Business Data Web Parts.
What Is Business Connectivity Services?
BCS bridges the gap between various applications that a company uses and the company’s need to surface key business data from those applications into SharePoint. These applications can include Siebel, CRM, and SAP, and the data from the applications needs to appear in SharePoint sites, lists, search functions, and user profiles In the context of BCS, these applications are known as external systems By using BCS, a company can accomplish the following objectives:
· Reduce or eliminate the code required to access lineofbusiness (LOB) systems
· Achieve deeper integration of data in places where a user works
· Centralize deployment of connection definitions for use by both BCS and Office applications. This capability is a major advantage of BCS over the connection methods described in Chapter 6, “Working with Data Sources,” where each site owner needs to acquire connection details of the external system data
· Reduce latency to access and manipulate data Once an external system is defined in BCS, the connection definition is available in all site collections in Web applications within the same service application group Then, in the browser, data from the external systems can be presented by using the XSLT List View (XLV) Web Part on Web pages and business data columns in lists and libraries On SharePoint Server Enterprise edition, the Business Data Web Parts are available to present data from the external systems In addition, users who can use SharePoint Designer on their sites can create a DFWP Both an XLV Web Part and a DFWP can be used to create views or data entry forms that can create, read, update, and display (CRUD) data from the external systems
· Centralize data security and auditing
· Perform structured data searches when a company uses SharePoint Server
Note You cannot complete the BCS central administration of external systems by using SharePoint Designer. Your server administrator can delegate these tasks to you, but you need to use the SharePoint 2010 Central Administration Web site to complete these tasks. The description of these tasks is outside the scope of this book.
See Also More information on managing BCS from a SharePoint administrator’s perspective can be found in Microsoft SharePoint 2010 Administrator’s Companion, by Bill English, Brian Alderman, and Mark Ferraz (Microsoft Press, 2011) and at technet.microsoft.com/enus/library/ ee661742.aspx.
The BCS is divided into three areas:
1. Connectivity: Before you can access data from an external system, SharePoint must know how to connect to that external system and the authentication method to use Then you can define the data you want to use from the external system The connection information is stored in a Business Data Connectivity (BDC) model that is used to create external content types (ECT), also known as entities
2. Presentation: External data can be presented in Office 2010 applications and in SharePoint by using external lists, business data columns, business data search, user profile properties, and Web Parts An external list is created from an ECT The connectivity layer of BCS, the BDC layer, uses the information in the ECT to connect to the external system to display the data in the external list.
3. Tools: SharePoint Designer 2010 and Microsoft Visual Studio 2010 are ECT designers, as are some thirdparty tools, such as BCS Meta Man from LightningTools, which can be found at lightningtools.com/ The ECT is the basic building block for using external data within SharePoint These tools allow information workers, business analysts, and developers to define the BDC model, author the ECT, and create dashboards and composite applications based on data from the external systems.
See AlsoYou can learn more about external content types in the SharePoint SDK at msdn. microsoft.com/enus/library/ee556391(office.14).aspx and on the Microsoft Business Connectivity Services Team blog at blogs.msdn.com/b/bcs.
The BCS components (objects)—which include external data source definitions, ECTs, and their associated operations—are stored centrally in a BCS SQL Server database, known as the BDC Metadata Store. Therefore, once they are created, the BCS components are available for use by other sites. Because BCS components are not stored as part of a site, they are not included in a site template. However, you can export the definitions into an XML file known as the BDC model file. Then, using the SharePoint 2010 Central Administration Web site or Windows PowerShell, the BDC model can be imported into a SharePoint installation. You use the BDC model file when you want to connect directly to the external system from an Office 2010 application.
The easiest and most effective way to connect to an external system is to use SharePoint Designer The advantage of using BCS over the methods described in Chapter 6 is that you need to define external data source connections only once as an ECT, and then you can use that ECT on many sites You also have the capability of using single sign on when the Secure Store Service (SSS) is configured on a SharePoint Server 2010 installation The disadvantage is that you need to enlist the assistance of the server administrator, because ECTs are stored centrally in the BDC Metadata Store, and not everyone in your organization will have permissions to create or modify ECTs. (In comparison, site owners can create site level data source definitions, and you do not need any special permissions assigned to you by the server administrator)
To connect and retrieve data from an external system, the following tasks are required:
· Create an external data source connection
· Define the operations to retrieve, modify, and delete content stored in that data source as appropriate to your business requirements
· Create an ECT based on an external data source
· Use the ECT to present the data from the external data source as an external list, an external data column, a Web Part, or within an Office application
Creating Database External Data Sources
To connect to external systems, you need to know the data source type, connection properties, and the operations you want to use on the data The following data source types can be used:
· Cloudbased services
· Windows Communication Foundation (WCF) endpoints
· Web services
· .NET assemblies that can gather data from multiple sources
· Custom external systems that have a nonstatic interface that changes dynamically
With SharePoint Designer you can only define external systems that use the data source types SQL Server, NET, and WCF Service To define external systems using other data source types, you must use an alternate tool
See Also You might have external systems that you want to connect to using SharePoint Designer but that do not provide an interface that you can use. One method to work around this situation is to expose the data from your external system as a Web service. The Microsoft Business Connectivity Services team has a two-part blog titled “Making Web Services BCS Friendly.” The first part of the series can be found at blogs.msdn.com/b/bcs/ archive/2009/11/18/makingwebservicesbcsfriendlypart1.aspx.
The connection properties include the authentication mechanism you are going to use to connect to the external data source External data sources are not an integral component of a SharePoint installation, so you need to configure the authentication method that will be used to retrieve, modify, and delete (if appropriate) the data from an external data source In SharePoint Designer the following BCS authentication modes can be defined:
· User’s Identity – When a user requests a SharePoint page that displays data from an external data source, SharePoint sends the user’s credentials to the external data source and allows the external data source to decide whether that user is allowed access. In most installations, to use Windows authentication credentials, your server administrator needs to configure a computer network authentication protocol named Kerberos; otherwise, a login failed message is displayed Your server administrator might know this authentication mode as PassThrought
· Impersonate Windows Identity – Both this and the next authentication mode require the use of SSS and are available only on SharePoint Server
· Impersonate Custom Identity – Credentials mapped in a database are sent to the external data source
· BDC Identity- SharePoint passes a special user name to the external data source for authentication purposes This user name has a high level of privileges on a SharePoint installation Therefore, Microsoft does not recommend the use of this authentication mode, and it is disabled by default BDC Identity is also known as RevertToSelf.
See Also For more information on authenticating to your external system and the BCS builtin permissions, see the Microsoft Business Connectivity Services team blogs at blogs.msdn. com/b/bcs/archive/2010/03/12/authenticatingtoyourexternalsystem.aspx, blogs.msdn. com/b/bcs/archive/2009/11/24/permissionsinbusinessconnectivityservices.aspx, and blogs. msdn.com/b/ericwhite/archive/2010/06/18/consumingaclaimsenabledwcfwebserviceas¬ansharepoint2010externalcontenttype.aspx.
You can define or modify external data sources only by using the Operations Design view of an ECT settings page To define external system content source definitions, ECT operations, and ECTs in SharePoint Designer, your server administrator must give you the Edit permission on the Metadata Store
In this exercise, you create an external data source for a SQL Server database
15. On the Quick Access Toolbar, click Save.
A dialog box is displayed briefly, stating that SharePoint is storing the ECT to the BDC Metadata Store
Troubleshooting– If an error dialog box is displayed stating that saving the ECT failed, you are probably saving an ECT without connecting it to an external content source and have not defined at least one operation on that content source. Complete the previous two exercises, and then return to this step.
The label name changes to SPD SBS Orders, and the asterisk disappears
CLEAN UP -Leave SharePoint Designer open if you are continuing to the next exercise.
Creating Office Application External Content Types
Microsoft made it possible to display external data in Office applications. You can also take the external data offline by using Microsoft Outlook and Microsoft SharePoint Workspace. A user can connect external lists with these two Office applications. Behind the scenes, a BDC model is packaged and deployed to the user’s computer Microsoft categorizes this type of a solution, in which a user clicks a button to deploy the BDC model, as a BCS simple solution To assist in tight integration, especially with Microsoft Outlook and external lists, before you define the operations of an ECT, you must specify whether a user is allowed to take the data presented in an external list offline You also need to configure the Office item type, of which there are four:
When you define the data source operations, you map data source elements to Office properties The mapping of elements to Office properties can be configured only when an operation is created You cannot change the Office item type or the mappings after the operations are created Be sure you plan the current and future use of your ECTs before you create them. Also, you can only choose to map those elements that have a compatible Office properties data type.
In this exercise, you create a new ECT and map it to the Contact Office item type.
SET UP- Using SharePoint Designer, open the team site you used in the previous exercise if it is not already open.
1 In the Navigation pane, click External Content Types, and then on the External Content Types tab, click External Content Type.
2 In the External Content Type Information area, to the right of Name, click New external content type, and then type SPD SBS Customers.
3 To the right of Display Name, click new external content type. SPD SBS Customers is copied to Display Name.
4 In the Office Item Type list, select Contact.
5. In the External Content Type Information area, to the right of External System, click Click here to discover external data sources and define operations.
The Operations Design view of the ECT is displayed.
6. On the Data Source Explorer tab, right-click a table, such as Customers, and then click the operation you want to create, such as Create All Operations.
7. In the All operations dialog box, click Next.
The Parameters Configuration page is displayed.
8. Under Data Source Elements, select CompanyName (be sure you do not clear the check box), and then under Properties, in the Office Property list, select Company Name (CompanyName).
9. Repeat the previous step to map the following data resource elements to matching Office properties:
Data source element
Full Name (FullName)
Job Title (JobTitle)
Business Address (BusinessAddress)
Business Address City (BusinessAddressCity)
Business Address State (BusinessAddressState)
Business Address Postal Code (BusinessAddressPostalCode)
Business Address Country/Region (BusinessAddressCountry)
Business Telephone Number (BusinessTelephoneNumber)
Business Fax Number (BusinessFaxNumber)
10. Click Next, and then click Finish.
The All Operations dialog box closes, and the five operations are listed in Operations Design view.
11. Right-click the SPD SBS Customers tab, and then click Save.
12. On the workspace breadcrumb, click External Content Types.
The External Content Types gallery page is displayed.
CLEAN UP – Leave SharePoint Designer open if you are continuing to the next exercise.
Creating and Managing External Lists
No matter which version of SharePoint 2010 you have installed, the preferred method of displaying data from external data sources is to use an external list. Depending on the operations you have specified in an ECT, an external list can provide CRUD capabilities. The external list can also be added to a SharePoint page as an XLV Web Part or a Data View. This lets you configure additional columns, conditional formatting, and sorting and grouping in the browser and in SharePoint Designer.
Note If you have SharePoint Server 2010 Enterprise edition, you can also use the Business Data Web Parts.
You create external lists by using the browser, SharePoint Designer, Windows PowerShell, or code. After you create these lists, you will find that they have similar functionally to other SharePoint lists. However, you cannot associate RSS feeds to external lists. There is also no Datasheet view, nor can you bind workflows to the data—because the data is not in SharePoint, you cannot trigger workflows on data changes. However, using SharePoint Designer, you can create a site, list, or reusable workflow that accesses one or more external lists.
Although you might have permissions to create a BDC model, an ECT, and external lists, you might not be able to see the data in the external list or on a page that contains an external list XLV Web Part. Using the SharePoint 2010 Central Administration Web site, a server administrator can set permissions on data source elements, and once you have created an ECT, your SharePoint installation can be configured to not allow anyone to see the data from the external data source. However, other systems or users—those using Office applications, for example—might be able to connect directly to the external system without using the BCS permissions you have configured in SharePoint. You might need to contact your server administrator before you can progress further with your solution.
External lists can be created and managed from the Lists and Libraries gallery page, the Data Sources gallery page, and from the ECT settings page.