External Content Types are similar to regular content types in that they contain several fields that make up that particular content type. The main difference is that these fields are mapped to columns in the SQL database in this case. We are only going to be performing read operations for the purposes of search, but you could easily add write operations if you wanted to treat this like a regular SharePoint list. There are a lot of limitations though if you do, like no workflows, version history, folders etc.
We will be performing the following actions:
1. Create a new External Content Type
2. Create a new data connection
3. Create Read Item Operations
4. Create Read List Operations
In order to map the data, we want to SharePoint we now have to open up SharePoint Designer 2013.
Open the Site Collection you want to house the External Content Type Profiles
Create the External Content Type
Click on “External Content Types”
Click to Create a “New External Content Type”
Enter a name and display name for the new external content type we are creating then click “Click here to discover external data sources and define operations.
Click on “Add Connection”
We want to connect to “SQL Server”
Enter the name of your SQL server and the name of the database. Since we created a Windows account for our target application we will want to select that here. Enter the name of the Application we created in the Secure Store Service.
Here you can enter in your own credentials assuming they are a member of the secure store application.
Now you can review your data source. In the AdventureWorks sample, we have several views already created for us.
Right click on “vProductAndDescription”
Click “New Read Item Operation”. We will only be created this operation since we only gave read access to the database and we want to return individual items and not a list.
I like to rename all my operations and fields to make it easier to find in SharePoint’s Search Schema. There are no errors so let's go ahead and click "Next".
Here we need to select a unique value. So let's pick ProductID since that will be unique to each item. Rename the field and if there are no errors click next.
Now map each field to a new one in SharePoint and click Finish. The warning message does not apply in this scenario since we are performing read-only operations.
Now Right Click on vProductModelCatalogDescription and click “New Read Item Operation” This is the operation that we will be displayed in our search results and profile pages.
Rename the operation and click “Next”
Map the ProductModelID to an Identifier.
Map all of the fields and prepend “AdventureWorksModel” for organizational purposes.
For this step, we are just going to go through the same steps as before to create the read list operations. The read list operation is necessary for the crawler to be able to crawl each particular item in the view. The main difference is that SharePoint recommends a filter to limit the number of items returned.
Now click back on “Operations Design View”
Right click on “vProductModelCatalogDescription” and select “New Read List Operation”
Because we do not have very many items we can proceed without adding a filter.
Now you can save everything and move back to Central Administration.
Here we will be creating the profile page for the items in the database as well as managing the permissions for both users and the search crawler. This is an important step often missed, leading to several authentication errors in the search crawl.
The steps for this actions are:
1. Create the Profile Page
2. Configure Object and Metadata Store Permissions
Click “Business Data Connectivity Service”
Click on “Configure”
Enter the site we created to house the external content type profile pages.
Click on the dropdown for the external content type we created “AdventureWorksModel” and select “Create/Upgrade Profile Page”
Click “Open Content in a new window” This occurs because we are trying to open up a different location in the modal.
We will see the gray “This Shouldn’t take long” page.
When it is done click “OK” again
Click Set Metadata Store Permissions
Select Execute for All Users
Select all permissions for your admin user
Do the same for “Set Object Permissions”, which for some reason doesn’t have an inheritance from the Metadata Store.
Never miss a thing by signing up for our newsletter. We periodically send out important news, blogs, and other announcements. Don’t worry, we promise not to spam you.