Custom User Profile data from external LOB system

This post will walk through the steps needed to integrate an existing line of business (LOB) system with SharePoint’s user profile service application.
Scenario:
We want to pull in additional social data for our user’s that displays their current Facebook and Twitter handle and web address. We will utilizing the following:

• User Profile Service Application
• Custom properties in the UPA
• Twitter Handle
• Twitter Page
• Facebook Page
• User Profile Sync
• SharePoint Designer ECT (External Content Type)
• Business Connectivity Service Application

First off, we need to create an External Content Type in SharePoint designer that we will later use in our user profile service to pull data from. As you can see below, we have a basic SQL table [SocialData] that has the properties we need to feed into the User’s profile.

From SharePoint Designer create a new external content type and call it whatever you would like. I have called mine EmployeeSocialData.
Next we will add external data to this ECT. In this case we are using a direct SQL connection. However, you could chose to use a WCF service to retrieve your data. As you can see below we are utilizing a Secure Store ID to connect to our data. For this example I have mapped my farm creds this ID.

We need to create the Read List and Read Item operation on the table SocialData. You will notice that I did not have to change anything when configuring my List/item read operations. This is because I declared the userName column in my SQL table as the primary key. SharePoint uses that column as the identifier. If your table does not have this setup you need to manually configure which field will be your identifier.
NOTE: The identifier will be the filed that the User profile Service will use to create a relationship and return employee data. This will be shown in later steps.

Once we create the ECt it will show in our BDC Service App as shown below.

We need to setup the object permissions to allow the SP Farm account to query and pull data from the ECT. In this case I am allowing a single user. Because the SPFARM account is doing to call I give the SPFARM execute permissions. I also have to give an account the Set Permissions permission to advance this screen.

TIP: To test whether or not your ECT is working correctly, use designer to create a list based on the ECT. You can do this via the ribbon.

Now we can go to our new list and ensure that data is being returned. This will validate that everything is flowing correctly. As you can see below, we are seeing all of the SQL records in our list as expected.

Note: You can delete this list. It only for validation purposes. Alternatively, you create additional ECT operation to allow people to update this list as well.
Next we will create our new Sync Connection in Central Admin > Service Applications > User Profile Service Application > Synchronization Connections
Add a new BCS connection to your BCS ECT that were created earlier. As you will see below, we have created a new connection to our SQL database via BCS. We have also told SharePoint that we want the UPS to match the SharePoint UserName field to the primary key we identified in our ECT to make our 1:1 match.

Now we can configure our user profile service application. First we will create our custom properties.

Facebook Page
Twitter handle
Twitter page

For each property we need to create a property mapping to our BCS data connection. An example of the twitter handle field is below.

Do this for the remaining fields
Now we need to perform a full sync. If you check the FIM client you will see that our ‘updates’ are being updated in the user profile as shown below.

We can check our user profile and see our new data has been synced!