Business Connectivity Services, External Content Types and Content By Search in SharePoint 2013 – Part 1

SharePoint 2013 makes it very easy to index data from external systems using Business Connectivity Services (BCS) and then to surface that data in SharePoint by taking advantage of the new Content By Search web part and Managed Navigation. In this blog post you’ll find step-by-step instructions on how to create External Content Types optimized for search and index data from external systems. My next blog post will build on top of that and will show how to configure Managed Navigation and Content By Search web parts to retrieve and display the external system data on a SharePoint site.

External System

In this example I’ll be using a copy of the AdventureWorks 2012 database from Codeplex. You can download the SQL Server 2012 OLTP version of the database here: http://msftdbprodsamples.codeplex.com/

External Content Types

We’ll need to create two external content types based on the database entities – Product and ProductModel.

  1. Launch Microsoft SharePoint Designer 2013.
  2. Open the SharePoint site where would you like to create the External Content Types.
  3. Select External Content Types in the left Site Objects pane and press the New External Content Type button in the ribbon.
  4. Set the Name to Product.
  5. Click the link next to the External System to bring up the Operation Designer.
  6. Press Add Connection, select SQL Server and configure the Connection Properties.
  7. In the Data Source Explorer, expand AdventureWorks2012 > Views.
  8. Generate the New Read Item Operation and New Read List Operation for vProductAndDescription view by right-clicking it. Map the ProductID column to Identifier.
  9. Save changes.
  10. Navigate back to the External Content Types screen and click the Product name to bring the content type back up.
  11. In the Fields section, select the Name field and press Set as Title in the ribbon. This will ensure that the Name field will appear as the title of the record in search results.
  12. Save changes.
  13. Repeat the steps above to create the ProductModel external content type. Use the vProductModelCatalogDescription view, ProductModelID as identifier and set the Name field as title.

Once all of the steps above are complete you’ll need to configure some additional settings in Central Administration. First we need to configure permissions.

  1. Open Central Administration.
  2. Navigate to Manage service applications and select the Business Data Connectivity Service Application then press Manage in the ribbon or simply click the service application name.
  3. Grant your search default content access account permissions to the metadata store or individual objects by using the Set Object Permissions and Set Metadata Store Permissions in the ribbon.

Now let’s add default actions to the ProductModel and Product external content types. The default action

  1. Click the ProductModel external content type.
  2. Press the Add Action button in the ribbon.
  3. Set the URL to something like http://www.contoso.com/products/{0} – this is going to be the location to the product rollup page on the publishing site.
  4. Press Add Parameter and select the Name field.
  5. Check the Default action checkbox and press OK.
  6. Repeat the steps above for the Product external content type but use http://www.contoso.com/products/{0}/{1} as the url, ProductModel field as the first parameter and ProductID field as the second parameter.

Search

At this point we are almost done and are ready to crawl and index the data.

  1. Navigate to the Search Service Application in Central Administration.
  2. Click Content Sources link in the left navigation section under Crawling.
  3. Click New Content Source.
  4. Set the Name to AdventureWorks2012.
  5. Select Line of Business Data as the Content Source Type.
  6. Select the Business Data Connectivity Service Application in the dropdown.
  7. Select the Crawl selected external data source option and check the checkbox next to AdventureWorks2012.
  8. Press OK.
  9. Start Full Crawl for the newly added AdventureWorks2012 content source.

When the crawl is done, navigate to the Search Center site and run a search query for contentsource:AdventureWorks2012. You should now be getting search results back. In my next blog post I’ll show how to surface these search results on the SharePoint site using Managed Navigation and Content By Search web parts.