Extend Active Directory People Search Result by using Business Connectivity Service - part 1

Article
10/20/2011

Scenario:

I configured FAST Search for SharePoint 2010 to crawl Active Directory User profiles but there are some related information located in SQL server Table in HR system which includes the Department Name and Employee ID.

So I need to Link the information in Active directory with the Information in the Table so when I search for the user I get more details from different sources.

Environment:

SharePoint 2010.
FAST Search for SharePoint 2010.
SQL Server 2008 R2.
SharePoint Designer 2010

Assumptions:

User Profile Service is configured and started.
Secure Store Service is configured and started.
User Profile configured as content source and User Can search for Profiles by using FAST Search Site.

Steps for this Scenario:

Create a Table and fill it with some test data and create a stored procedure which Get Employee by Email.
Create External Content Type for SQL Server Table
Add connection between Active Directory User Profile and BCS content source and then Add User profile properties and start full synchronization.
Run Incremental Crawl for User Profile content source and Add Managed properties.
Customize People Search Result Page.

Requirements:

Link between the Active directory User Profile and other information in external system link HR.
Add Department Name to Custom Refinement Panel ,Core People Result and Search Options.

Steps to do:

Step 1- Create a Table and fill it with some test data and create a stored procedure which Get Employee by Email.

1- Create Test Table Called "Employees" which contains the following schema:

 CREATE TABLE [dbo].[Employees](
   [EmpId] [int] IDENTITY(1,1) NOT NULL,
   [Email] [nvarchar](256) NOT NULL,
   [DeptName] [nvarchar](256) NOT NULL,
 CONSTRAINT [PK_Employees] PRIMARY KEY CLUSTERED 
(
  [EmpId] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF,

 ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

GO

     2- And then fill it with some test data:

   3- Now create stored procedure which Return the information of Employee by Email Id

 CREATE PROC [dbo].[bcs_spGetEmployeesByEmail]

@Email   NVARCHAR(256)

AS

SELECT  EmpId,
        Email,
        DeptName
        FROM [dbo].Employees
        WHERE Email = @Email

Note: Why by Email?

 Because in my case the common column (which exists in both source so I will use it as connection

  column) between Active Directory User Profile and Profile in SQL Server Table.

  ************ Step 1 Done ************

 Step 2- Create External Content Type for SQL Server Table

 1 -Open Microsoft SharePoint Designer and Open FAST Search Site.

 2- Go to External Content Types and Create New External Content Type

 3- Fill the Fields as the below image:

 4-"Click here to discover external data sources and define operations" to define external system.

 5- Click add connection and choose "SQL Server"

 6- Define SQL Server Connection.

Note: You should have Secure Store Application ID which has SQL Server Authorization Information (User Name and Password) to connect to SQL Server Database.

7- Open Routines Folder and Right Click on " bcs_spGetEmployeesByEmail" stored procedure and choose New Read Item Operation

8- Leave the Default Values and Click Next

9- Leave the Default Values and Click Next

10- Map Email to identifier Email and then Click Finish

11- Then Save the External Content Type

12- Open Business Connectivity Service Application through Central administration to Set Permissions to the user account (default access user account in Search)

and Click Ok.

************ Step 2 Done ************

Step 3- Add connection between Active Directory User Profile and BCS content source and then Add User profile properties and start full synchronization.

So to Add connection between User Profile in Active Directory and BCS content source .

1- Open User Profile Service application through Central administration

2- Click on Configure Synchronization connections