Thursday, May 9, 2013

Double AD profiles in User Profile Service?

This is directly related to working with Claims.

The user profile service imports AD accounts and sets the 'identifier' of the object to be the sAMAAccountName (This is the AD property which looks like sampledomain\johndoe).

When a claims web app accesses the User Profile service, such as for example MySites, if configured to use Claims, it looks for the User Profile service by it's own identifier, which is the token in a format like this:  i:0#.f|ldapmember|johndoe

The object will not be found, and MySites will generate the user profile and set the url to the personal site (yet another property in User Profiles), and this is how you end up with two records that refer to the same person, such as below:

The first one is the one generated by the User Profile Service, with properties such as First name, Last Name and other properties you have mapped to be imported from AD, while the other profile is the one generated by MySites, and only has the token and the URL set to the personal site.

The solution is to map the two identifiers to each-other so that when a claims-based app queries the User Profile service, it finds the profile by token.

The first property, Claim User Identifier, refers to the token and the mapped AD property called sAMAAccountName refers to the format domain\user.

Once mapped, any user profile action will follow this rule. For already existing duplicate accounts, the duplicate token one needs to be deleted for clean-up.

The new MySite generations should look like this:

This solution assumes that when you set up the UPS AD connection, you configure it as well as Claims:

Saturday, May 4, 2013

Migrating CSV data into SharePoint lists

A common migration scenario of data into SharePoint involves CSV files to be imported via Datasheet view into SharePoint lists.

However there are some limitations such as multi-lookup values, where data should be provided in the following format: "5;#technology;#3science;#". The same format has to be provided for columns of type 'People or Groups', such as "67;#John Doe;#123;#Anne Jackson;#". As a result, you cannot use DataSheet view and you have to upload the CSV programmatically.

This post focuses on reading the CSV file programmatically. You have two options:
  • if you use Powershell, you can use the PS command import-csv and then access the data like this:

    import-csv c:\folder\file.csv
    foreach($line in $excelFile){$title = $ 

  • if you use C# you can read the CSV file via a OLEDB driver you need to install locally, to permit your 64-bit code (you have to run on 64 bit to be able to execute SharePoint API calls for the actual lookup of values and list item creation).

    You can find the driver here Microsoft Access Database Engine 2010 Redistributable.
    This driver is simply a replacement for JET OLEDB to be run on server applications. JET is only running on 32 bits.

    string connectionString = @"Provider=Microsoft.ACE.OLEDB.12.0;Data Source=c:\folder_name\;Extended Properties='text;HDR=Yes;FMT=Delimited'";

    string excelSheetName = "file.csv";

    OleDbConnection myConnection = new OleDbConnection(connectionString);
    OleDbCommand myCommand = new OleDbCommand("Select * from " + excelSheetName + ";", myConnection);
    OleDbDataAdapter adapter = new OleDbDataAdapter(myCommand);