Getting the Imported SharePoint Id’s from the User Profiles in SharePoint 2010

Posted: 24th March 2011 in Sharepoint 2010, SQL, x64
Okay, here’s the scenario:
You’ve set up the User Profile Synchronisation using the Microsoft Documentation, and your now at the situation where have the Active Directory Accounts imported into SharePoint. Now, you want to do a data load and match up people picker fields to the actual profiles in the SharePoint format of ID;#FirstName LastName. However, to do this you need to find out what the Id of each user is.
So, here how I went about getting them.
Firstly, the profile import throws all the users into the Profile DB you have set up in the previous step. So, open SQL Server Management Studio –> Profile DB  and return all the user profile records by running the following query
Insert List Item Method
  1. SELECT [PreferredName] ,[Email]
  2. FROM [Profile DB].[dbo].[UserProfile_Full]
Next step is to copy all the returned email addresses into a file and save it somewhere, were going to use the data in a minute.
Now create an console application, remembering to set the framework to 3.5 and the target processor to x64 as I’ve described in a previous blog.
Now, add the text file to the root of the project as an embedded resource.
and the copy in the following code into your program.cs . You will need to change the constant values to suit your environment and add
using Microsoft.Office.Server;
using Microsoft.SharePoint;
Insert List Item Method
  1. class Program
  2. {
  3. private const string siteUrl = http://sharepoint/”;
  4. private const string subSite = “hr”;
  5. private const string outputFileLocation = @”C:\Temp\SharePointIds.txt”;
  6. static void Main(string[] args)
  7. {
  8. Dictionary<string, string> userIdEmailMapping =  GetUserProfiles();
  9. using (StreamWriter writer = new StreamWriter(this.outputFileLocation))
  10. {
  11. foreach (var item in userIdEmailMapping)
  12. {
  13. StringBuilder sb = new StringBuilder();
  14. sb.AppendFormat(“{0},{1}”, item.Key, item.Value);
  15. writer.WriteLine(sb.ToString());
  16. }
  17. writer.Flush();
  18. }
  19. }
  20. private static Dictionary<string, string> GetUserProfiles()
  21. {
  22. Dictionary<string, string> userIdEmailMapping = new Dictionary<string, string>();
  23. using (SPSite site = new SPSite(this.siteUrl))
  24. {
  25. using (SPWeb web = site.OpenWeb(this.subSite))
  26. {
  27. using (StreamReader reader = new StreamReader(Assembly.GetExecutingAssembly().GetManifestResourceStream(Assembly.GetExecutingAssembly().GetName().Name + “.UserEmails.txt”)))
  28. {
  29. while (!reader.EndOfStream)
  30. {
  31. var email = reader.ReadLine();
  32. try
  33. {
  34. SPUser user = web.AllUsers.GetByEmail(email);
  35. userIdEmailMapping.Add(user.Email, user.ID.ToString());
  36. }
  37. catch (Exception ex)
  38. {
  39. userIdEmailMapping.Add(email, ex.Message);
  40. }
  41. }
  42. }
  43. }
  44. }
  45. return userIdEmailMapping;
  46. }
  47. }
Compile the exe, copy it your server and run it as administrator and it should populate the output file in a comma separated list with the ids which are in use.
However, there is one snag.
SPWeb.AllUsers  only returns the user who have actual been parsed in the site. So, to get round this, create a new column on list as a people picker and allow it to hold multiple values. Then, take your list of AD users, who don’t have Id yet and copy them into the people picker and save the list item. This should now create id for those users. I’m not sure if there is a limit for the people picker fields but I successfully added 30. Massive domains may be a bit harder though.
Oh well that’s about it. Cheers Winking smile

Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s