In most of my projects I had the requirement of storing additional data to SharePoint User. For these scenarios SharePoint user profiles match best because they are flexible and easy to extend with a lot of different data types.
In this article I show a fast and flexible way to import data to SharePoint Online User Profiles. The import data can be stored in a tab separated text file. There are several challenges to master until the data can be imported.
REST API vs. User Profile Web Service
In general it is recommended to use the new REST APIs. These new APIs provide methods to read and write data but the user profile REST api is one big exception. This only allows to read data. The only thing that can be changed is the user profile image.
To write data to the SharePoint Online User Profiles the SOAP web service needs to be used. In an on-premise installation this web service can be accessed from any site collection in a SharePoint Farm. The location of this web service is /_vti_bin/userprofileservice.asmx. Sadly this is not possible in the cloud.
In Office 365 there is only one location for accessing this web service that works. This can be found in the SharePoint Administration. The base url to the SharePoint Administration is http://yourcompany-admin.sharepoint.com/ . The part “yourcompany” is dynamic and needs replaced by your subscription name. If this base url will be extended by the location of the user profile web service we get a working instance. The url is then will look like http://yourcompany-admin.sharepoint.com/_vti_bin/userprofileservice.asmx.
Authenticating against the web service
The next challenge that needs to be mastered is how to authenticate against this web service. In an on-premise installation the authentication is simple. All that needs to be passed to the web service are valid credentials defined by username and password. In Office 365 the authentication is a bit different and an authentication cookie needs to be passed.
The longer explanation how to authenticate against Office 365 work can be found in the MSDN article Remote Authentication in SharePoint Online Using Claims-Based Authentication. Another great source of information is the blog post “How to do active authentication to Office 365 and SharePoint Online” by Wictor Wilén. In this article Wictor provides a helper object that makes the authentication process easy. His solution is mainly targeted on SharePoint 2010 in Office 365 but this still works in the recent version of SharePoint Online.
Possibly the best way to authenticate is by using newly with SharePoint 2013 introduced SharePointOnlineCredentials object. With only a couple lines of code the authentication succeeds.
// Pass the username and password to SharePointOnlineCredentials constructor
// Password needs to be passed as a System.Security.SecureString
SharePointOnlineCredentials onlineCred = new SharePointOnlineCredentials(_username, mySecurePassword);
// Get the authentication cookie by passing the url of the web service
string authCookie = onlineCred.GetAuthenticationCookie(_adminUrl);
// Create a CookieContainer to authenticate against the web service
CookieContainer authContainer = new CookieContainer();
// Put the authenticationCookie string in the container
// Setting up the user profile web service
UserProfileWS.UserProfileService upService = new UserProfileWS.UserProfileService();
// assign the correct url to the web service
upService.Url = _adminUrl.AbsoluteUri;
// Assign previously created auth container to web service
upService.CookieContainer = authContainer;
All of the code above requires parts of the .net framework and the managed client object model of SharePoint.
Data source to import user profile data
Now with all the parts together we are ready to build a bulk updater. I won’t go too much into detail on the code because the rest is pretty much straight forward and can be found in the downloadable solution below. Let’s just take a look how the importer works and part of this process is to have a working data source.
As mentioned in the introduction the importer works with a tab separated text file. In this file the first line needs to contain all the property names that should be imported. The only required property in there is the property “AccountName” because this will be used to find the user. The other columns need to have the name of the properties. I don’t use the display names because they are not unique and can vary by language.
By adding the demanded properties the importer is flexible and don’t need to be adapted in the code. The following screenshot shows a sample import file.
This file contains only the birthday (SPS-Birthday) and the skill (SPS-Skills) property. The skill property is a multi value field that stores the terms directly to the keyword store. The single values can be delimited by a comma or semi-colon.
Even other user accounts can be specified for example if the manager attribute needs to be filled. The claim encoded user name needs to be added to the text file in a format like this i:firstname.lastname@example.org .
Steps to update the user profiles
Now that the data source is defined the updater executes the following steps.
- Authenticate against SharePoint Online Administration to get the authentication cookie and pass it to the user profile web service
- Open the file and read the first line to get a list of properties that should be imported
- Read the rest of the file and try to update the profiles
To execute the updater needs the following parameter in order to work.
- URL to the SharePoint Administration
- Username that should be used to authenticate
- Path to the data source
After the update the profile properties look like this:
The new API’s are great but some things still need to be done the old fashion way until they provide the same functionality. I hope this tool will help during the migration process from an on-premise installation to the cloud.
I think this is also a great opportunity to enrich the SharePoint Online user profiles in general. Instead of asking the user to enter their data once again.
** Update 30th June 2014 **
For those who are looking for a complete list of default properties that matches the Display Name of the fields. This list can be found on the Technet Default user profile properties (SharePoint 2010)
I tested this tool in many ways. Before you do a bulk update on all user profiles please re-test it, just with a single test user and make sure that the data have been written to SharePoint Online correctly. Also make sure that the data source is well formatted.
You will use it at your own risk.