User Profile Imports using BCS

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.



Figure : BCS Database in SQL Server

EmployeeDetails table:

EmployeeDetails table contains the following columns

Figure : EmployeeDetails table column names and data types

EmployeeDetails table contains the following rows



Figure : EmployeeDetails table rows

Create New User Properties

1. Open Central Administration by going Start | All Programs | Microsoft SharePoint 2010 Products | SharePoint 2010 Central Administration.
2. Click on Manage Service Application which is available in Application Management section.
   
   
   
   Figure: Application Management section in Central Administration
    
3. Click on User Profile Service Application.
4. Click on Manage User Properties in the People section.
   
   
   
   Figure: Manage User properties in People section
    
5. Click on New Property.
   
   
   
   Figure: New Property link
    
6. Enter the Name as EmpID, Display Name as EmpID and select integer from the Type dropdown as shown in Figure.
   
   
   
   Figure: Enter the details for the new property EmpID
    
7. Click on Ok.
8. A new custom user property "EmpID" is created successfully and it will be available in Custom Properties section.
9. Create another custom property named "Designation".
10. Click on New Property.
11. Enter the Name as Designation, Display Name as Designation and select string (Single Value) from the Type dropdown, as shown in Figure.
12. Enter the Length value as 50 because in the SQL EmployeeDetails table Designation column has a type nvarchar (50), as shown in Figure.
    
    
    
    Figure: Enter the details for the new property Designation
     
13. In the Policy Settings, select Optional from the Policy Setting dropdown and select Everyone from Default Policy Setting dropdown, as shown in Figure.
    
    
    
    Figure: Policy Settings for Designation property
     
14. Click on Ok.
15. A new custom user property "Designation" is created successfully and it will be available in Custom Properties section.

Configure Synchronization Connection

1. In the Central Administration, click on Manage Service Application which is available in Application Management section.
2. Click on User Profile Service Application.
3. Click on Configure Synchronization Connections in the Synchronization section.
   
   
   
   Figure: Configure Synchronization Connections in Synchronization connection
    
4. Click on Create New Connection.
   
   
   
   Figure: Create New Connection
    
5. 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.
   
   
   
   Figure: Create new synchronization connection
    
6. "External Content Type Picker – Webpage Dialog" will pop up, select the external content type "Employee Details ECT".
   
   
   
   Figure: External Content Type Picker
    
7. Click on Ok.
8. Select EmpID from the dropdown for connecting User Profile Store to Business Data Connectivity Entity as a 1:1 mapping, as shown in Figure.
   
   
   
   Figure: Connect User Profile Store to Business Data Connectivity Entity as a 1:1 mapping
    
9. Click on Ok.
10. BCS synchronization connection is created successfully as shown in the Figure.
    
    
    
    Figure: New synchronization connection BCS created

Add Mapping to User Property

1. In the Central Administration, click on Manage Service Application which is available in Application Management section.
2. Click on User Profile Service Application.
3. Click on Manage User Properties in the People section.
4. Select the Designation property in the Custom Properties section.
5. In the ECB menu, click on Edit.
6. 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.
   
   
   
   Figure: Add Mapping to Designation user property
    
7. Click on Add.
8. New mapping is added to the Designation user property.
9. Click on Ok.

Edit the user profile

1. In the Central Administration, click on Manage Service Application which is available in Application Management section.
2. Click on User Profile Service Application.
3. Click on Manage User Profiles in the People section.
   
   
   
   Figure: Manage User Profiles in People section
    
4. I am going to modify the User Profile "Vijai" whose EmpID is 100 in the SQL EmployeeDetails table.
5. Select the User Profile, in the ECB menu click on Edit User Profile.
6. Enter the EmpID value as 100.
7. Click on Save & Close.
8. 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

1. In the Central Administration, click on Manage Service Application which is available in Application Management section.
2. Click on User Profile Service Application.
3. Click on Manage User Profiles in the People section.
   
   
   
   Figure: Start Profile Synchronization in Synchronization section
    
4. Select Start Full Synchronization option.
   
   
   
   Figure: Start Full Synchronization
    
5. Click on Ok.
6. Once the Full Synchronization is done Edit the User Profile and check.
7. Designation property will have the value imported from the SQL table as shown in the Figure.
   
   
   
   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.