User Profile Service Application is used to synchronize the data from Active
Directory to SharePoint. User Profile data is maintained by the Profile
Database in SharePoint. the profile Database is used to store and manage users' information. In some scenarios you may require getting the data for the user
profile from an external system. Here you will see how to get the data for the user
profile from a SQL database using Business Data Connectivity services. Here I will
be creating some custom user properties and map those user properties to the
columns in the SQL database so that those user properties will get the values
from the external system SQL database using BCS.
SQL Server Database Details
I will be using a SQL Server database as an external data source from where the
data will be imported for the user profiles in SharePoint 2010. A database named
"BCS Database" is created in SQL Server which contains the following tables.
![BCS1.gif]()
Figure : BCS Database in SQL Server
EmployeeDetails table:
EmployeeDetails table contains the following columns
![BCS2.gif]()
Figure : EmployeeDetails table column names and data types
EmployeeDetails table contains the following rows
![BCS3.gif]()
Figure : EmployeeDetails table rows
Create New User Properties
- Open Central Administration by going Start
| All Programs | Microsoft SharePoint 2010 Products | SharePoint 2010
Central Administration.
- Click on Manage Service Application which
is available in Application Management section.
![BCS4.gif]()
Figure: Application Management section in Central Administration
- Click on User Profile Service Application.
- Click on Manage User Properties in the
People section.
![BCS5.gif]()
Figure: Manage User properties in People section
- Click on New Property.
![BCS6.gif]()
Figure: New Property link
- Enter the Name as EmpID, Display Name as
EmpID and select integer from the Type dropdown as shown in Figure.
![BCS7.gif]()
Figure: Enter the details for the new property EmpID
- Click on Ok.
- A new custom user property "EmpID" is
created successfully and it will be available in Custom Properties section.
- Create another custom property named
"Designation".
- Click on New Property.
- Enter the Name as Designation, Display
Name as Designation and select string (Single Value) from the Type dropdown,
as shown in Figure.
- Enter the Length value as 50 because in
the SQL EmployeeDetails table Designation column has a type nvarchar (50),
as shown in Figure.
![BCS8.gif]()
Figure: Enter the details for the new property Designation
- In the Policy Settings, select Optional
from the Policy Setting dropdown and select Everyone from Default Policy
Setting dropdown, as shown in Figure.
![BCS9.gif]()
Figure: Policy Settings for Designation property
- Click on Ok.
- A new custom user property "Designation"
is created successfully and it will be available in Custom Properties
section.
Configure Synchronization Connection
- In the Central Administration, click on
Manage Service Application which is available in Application Management
section.
- Click on User Profile Service Application.
- Click on Configure Synchronization
Connections in the Synchronization section.
![BCS10.gif]()
Figure: Configure Synchronization Connections in Synchronization connection
- Click on Create New Connection.
![BCS11.gif]()
Figure: Create New Connection
- Enter the Connection Name as BCS, select
Business Connectivity Services from the Type dropdown and click on Select
External Content Type button, as shown in Figure.
![BCS12.gif]()
Figure: Create new synchronization connection
- "External Content Type Picker – Webpage
Dialog" will pop up, select the external content type "Employee Details ECT".
![BCS13.gif]()
Figure: External Content Type Picker
- Click on Ok.
- Select EmpID from the dropdown for
connecting User Profile Store to Business Data Connectivity Entity as a 1:1
mapping, as shown in Figure.
![BCS14.gif]()
Figure: Connect User Profile Store to Business Data Connectivity Entity as a
1:1 mapping
- Click on Ok.
- BCS synchronization connection is created
successfully as shown in the Figure.
![BCS15.gif]()
Figure: New synchronization connection BCS created
Add Mapping to User Property
- In the Central Administration, click on
Manage Service Application which is available in Application Management
section.
- Click on User Profile Service Application.
- Click on Manage User Properties in the
People section.
- Select the Designation property in the
Custom Properties section.
- In the ECB menu, click on Edit.
- In the Add New Mapping section, select BCS
from the Source Data Connection dropdown, Designation from the Attribute
dropdown and Import from the Direction dropdown, as shown in Figure.
![BCS16.gif]()
Figure: Add Mapping to Designation user property
- Click on Add.
- New mapping is added to the Designation
user property.
- Click on Ok.
Edit the user profile
- In the Central Administration, click on
Manage Service Application which is available in Application Management
section.
- Click on User Profile Service Application.
- Click on Manage User Profiles in the
People section.
![BCS17.gif]()
Figure: Manage User Profiles in People section
- I am going to modify the User Profile
"Vijai" whose EmpID is 100 in the SQL EmployeeDetails table.
- Select the User Profile, in the ECB menu
click on Edit User Profile.
- Enter the EmpID value as 100.
- Click on Save & Close.
- Once the full synchronization is done for
the User Profile "Vijai" the Designation property will have the value as
"Associate" which will be imported from SQL EmployeeDetails table.
Start Full Synchronization
- In the Central Administration, click on
Manage Service Application which is available in Application Management
section.
- Click on User Profile Service Application.
- Click on Manage User Profiles in the
People section.
![BCS18.gif]()
Figure: Start Profile Synchronization in Synchronization section
- Select Start Full Synchronization option.
![BCS19.gif]()
Figure: Start Full Synchronization
- Click on Ok.
- Once the Full Synchronization is done Edit
the User Profile and check.
- Designation property will have the value
imported from the SQL table as shown in the Figure.
![BCS20.gif]()
Figure: Value for Designation user property imported from SQL
database
In this article we have seen how to import
the data from external system like SQL database to SharePoint User Profiles
using BCS.