In the last couple of years, a lot of companies have started using BCS (Business Connectivity Services) to integrate data from external data sources into SharePoint. It was initially available with Microsoft Office SharePoint Server 2007 and was called Business Data Catalog. Since Version 2013, it is not only possible to read information but also to create, delete or update.
According to Mario Spies, senior strategic consultant at AvePoint, a lot of companies are in the process of moving their SharePoint content from on-premise to Office 365 / SharePoint Online, using tools such as DocAve Migrator from SharePoint 2010 or DocAve Content Manager from SharePoint 2013. In most of these projects, the question arises about how to handle SharePoint external lists connected to data using BDC.
The good news is that SharePoint Online also supports Business Connectivity Services.
Business Connectivity Services in SharePoint Online
Click through for eight steps organizations can take to integrate external data into SharePoint, as identified by Mario Spies, senior strategic consultant at AvePoint.
Select an Appropriate Microsoft Plan
The first step is to ensure that you have an appropriate Office 365 plan.
Microsoft Business Connectivity Services are only available in the Office 365 Enterprise E3 or E4 plans for business or, if you are in the education sector, you can use Education E2 licenses.
Consider the Limitations
Compared to the on-premises editions of SharePoint, there are still some limitations:
- No profile pages for external content types
- No offline availability or rich-client integration
- No search integration
Beyond these limitations, you can use BCS to connect to different data sources like WCF web services, O-Data sources and SQL Azure databases.
Create a Security Store Application
The first step is to create a target application in the Secure Store Service. This is needed in order to map a group of users to a single, external data account that can act on their behalf. In SharePoint Online, only group restricted credentials are possible. In on-premise versions, there are also individual and mappings available. For SQL Azure Databases, the credential type needs to be set to “Windows User Name” and “Windows Password.”
Set Up Connection and ECT
The easiest way to create a new external content type is to use SharePoint Designer 2013, which can be used to create external content types for Microsoft SQL Azure tables, views or stored procedures. You just need to open it and connect to your site collection using credentials having site collection admin permission. On the left-hand side is a menu item, “External Content Types.” The connection and all CRUD methods such as read list, read item, create, update and delete can be created from here.
The Read / Write Operations
Read List Operation: The Read List operation is like a select statement without a where clause. It returns by default all columns and all rows from the selected table. Filters can be created allowing the results to be trimmed. The Read List operation is used whenever multiple rows are displayed, such as within an external list or a Business Data List Web Part.
Read Item: The Read Item operation is created so that a specific row of data can be identified and used. Each table, view or stored procedure will require a column whereby each of its values is unique, such as a primary key. This column, called an identifier, uniquely identifies the row so that a specific row can be selected and used within SharePoint Web Parts, search, and the business data column. This method contains a ‘where’ clause that accepts a parameter passing the unique identifier value.
Create Operation: The Create Operation is used by the external list when data is being created. The Create Operation contains the statement allowing data to be inserted into the table.
Update Operation: The Update operation provides the ability to make and submit changes back to the external data source via the external list and some Office applications such as Microsoft Excel.
Delete Operation: The Delete Operation provides the ability to select a specific row and delete it via the external list.
Public Content Type and Create List
Now most of the work is done. By clicking save, the external content type is saved back to SharePoint.
The next step involved in this process is to create an external list that enables users to see and use the data. This is done by clicking “Create Lists & Forms” in the Ribbon. You can specify a name and the default operation for this list. Now you are able to create, read, update or delete items (depending on the permissions and created actions) in the SQL Azure database without leaving SharePoint.
Grant the Permissions
To make the newly created external content type available to business users, it is important to set the permissions in the Business Connectivity Service application. This is done through the SharePoint admin center. Select Manage BDC Models and External Content Types. Check the box next to the name of the content type you just created, and then click the Set Object Permissions button. In the following dialog box, select the check boxes for all the permissions that the SharePoint Online users need. Select propagate permissions to all methods of this external content type if you want to overwrite any existing permissions.
Conclusion
After migrating SharePoint from an on-premise system to SharePoint online, all external content types and lists will not work anymore because of missing BCS definitions. If these connections are still needed, there are two possible ways to get reconnected.
- Migrate Data to SQL Azure.
- Publish the line of business data as WCF web service.
This enables companies to continue line of business data after moving their on-premise environments to Office 365. Because a fair amount of manual work and planning is involved, customers should include consulting work into the project budget.
