Extend SharePoint 2013 People Search Using Business Connectivity Services Part 1

In this four part series I will describe the process of extending the SharePoint 2013 user profile from Active Directory with additional data from a SQL Server table.  The additional data will then be displayed in users’ My Sites and SharePoint people search.

Part 1 will show the process to create the SQL Server table.  Part 2 will show the process to make the data in the SQL Server table accessible to SharePoint.  Part 3 will show the process to extend user profiles and My Sites to include the additional user data.  Part 4 will show the process to make the new user profile properties accessible in SharePoint Search.

The SharePoint environment is SharePoint 2013 Standard, with SQL Server 2008 R2 and SharePoint Designer 2013.  The steps are based on an environment where the User Profile Service has already been setup, users have been imported from Active Directory and My Sites have been configured.  It is assumed that SharePoint Search has been configured and a search site using the Enterprise Search Center template has been created.

Firstly, create a SQL table to hold the user data.  Below is some example code that creates the table and adds an index on the email address column.

CREATE TABLE [dbo].[SPPeopleData](
[NameID] [int] NULL,
[FirmInitials] [varchar](6) NULL,
[Jurisdiction] [nvarchar](15) NULL,
[Email] [varchar](40) NULL
) ON [PRIMARY]

GO

CREATE NONCLUSTERED INDEX [_TW_Email] ON [dbo].[SPPeopleData]
(
[Email] ASC
) ON [PRIMARY]
GO

Next, fill the table with some test data.

BCS - Data

Now create a stored procedure to return user data for a given email address.  I’m using email address as it’s a property that’s imported for each user from Active Directory.

CREATE PROCEDURE [dbo].[twsp_GetSPPeopleDataForEmail] @Email VARCHAR(40) AS

SELECT [NameID]
,[FirmInitials]
,[Jurisdiction]
,[Email]
FROM [SPPeopleData]
WHERE [Email] = @Email

In part two I’ll show how to connect SharePoint to the data in the SQL Server table.

Advertisements

One thought on “Extend SharePoint 2013 People Search Using Business Connectivity Services Part 1

  1. Pingback: Extend SharePoint 2013 People Search Using Business Connectivity Services – Multi-Value User Properties | Richard Skinner

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s