Import User Profile Properties SharePoint Online

New Contributor

Hello all,

I need to sync all custom properties of user profil, some of the properties are done by AAD, but it's never appear on sharepoint online user profile properties, so i need to sync everything and finish the job that SPO can't do for know...(hope it will case in futur ;))). 

I use the basic API to queue the QueueImportProfileProperties : 
----part of the script that you easily found on the web----
$o365 = New-Object Microsoft.Online.SharePoint.TenantManagement.Office365Tenant($ctxImportJob)
$ctxImportJob.Load($o365)
# Type of user identifier ["Email", "CloudId", "PrincipalName"] in the User Profile Service
$userIdType=[Microsoft.Online.SharePoint.TenantManagement.ImportProfilePropertiesUserIdType]::Email
--------
$o365.QueueImportProfileProperties($userIdType, $userLookupKey, $propertyMap, $importFileUrl);
--------

I try to sync Local AD with this json template (and after i done the property Mapping...):
$ADUsers | Select-Object @{Label = "IdName";Expression = {$_.Mail}}, 
# @{Label = "LastName";Expression = {$_.Surname}}, #native >> active directory core properties sync with AAD
# @{Label = "FirstName";Expression = {$_.GivenName}}, #native >> active directory core properties sync with AAD
# @{Label = "PreferredName";Expression = {$_.DisplayName}}, #native >> active directory core properties sync with AAD
@{Label = "WorkEmail";Expression = {$_.Mail}}, #native >> active directory core properties sync with AAD
@{Label = "Department";Expression = {$_.extensionAttribute10 | Get-Department}},#native >> are Taxonomic Properties, active directory core properties
# @{Label = "JobTitle";Expression = {$_.extensionAttribute9}}, #custom >> are Taxonomic Properties, UPDATE with AD properties
# @{Label = "Manager";Expression = {%{(Get-AdUser $_.Manager -server $DC -Properties Mail).Mail}}}, #native >> active directory core properties
@{Label = "MyProfessionalCommunity";Expression = {$_.extensionAttribute3}}, #custom>> UPDATE with AD properties
@{Label = "WorkPhone";Expression = {$_.telephoneNumber}}, #native >> UPDATE with AD properties
@{Label = "CellPhone";Expression = {$_.mobile}}, #native >> UPDATE with AD properties
@{Label = "MyWorkplace";Expression = {$_.extensionAttribute8}}, #custom >> UPDATE with AD properties
@{Label = "Office";Expression = {$_.physicalDeliveryOfficeName}}, #native >> UPDATE with AD properties
# @{Label = "Location";Expression = {$_.l | Get-Location}}, #native >> are Taxonomic Properties, NOT SUPPORTED IN THE CURRENT API
@{Label = "LegalCountry";Expression = {$_.co}}, #custom >> UPDATE with AD properties
@{Label = "LegalCompany";Expression = {$_.Company}}, #custom >> UPDATE with AD properties
@{Label = "Assistant";Expression = {$_.assistant}}, #native >> UPDATE with AD properties
@{Label = "HireDate";Expression = {$_.extensionAttribute4 | ConvertTo-USDate}},#native >> UPDATE with AD properties
@{Label = "PublicSiteRedirect";Expression = {$_.wWWHomePage}}, #custom >> UPDATE with AD properties 
@{Label = "CompanyCode";Expression = {$_.extensionAttribute7}}, #custom >> UPDATE with AD properties
@{Label = "EmployeeStatus";Expression = {$_.extensionAttribute1}}, #custom >> UPDATE with AD properties
@{Label = "BusinessSector";Expression = {$_.extensionAttribute2}}, #custom >> UPDATE with AD properties 
@{Label = "BusinessDescription";Expression = {$_.extensionAttribute6}}, #custom >> UPDATE with AD properties 
@{Label = "JobDescription";Expression = {$_.extensionAttribute5}}, #custom >> UPDATE with AD properties 
# @{Label = "EmployeeLanguage";Expression = {$_.preferredLanguage}}, #native >> are editable by user
@{Label = "InternalFunction";Expression = {$_.extensionAttribute14}}, #custom >> UPDATE with AD properties
@{Label = "EmployeeNumber";Expression = {$_.employeeNumber}} | #custom >> UPDATE with AD properties
ConvertTo-Json -Compress | Out-File $jsonfile 

When i try to sync Department, it's failed and i suppose it's an internal bug or i need to do something special to set the value in a special format? but what, it's an asking string value...

SharePoint’s Online User Profile Properties 
GenericError System.Data.SqlClient.SqlException (0x80131904): Violation of PRIMARY KEY constraint 'PK_Profile_SearchUserTable'. Cannot insert duplicate key in object 'upa.Profile_SearchUserTable'. The duplicate key value is (92381304-82c2-4a79-a525-fd5d14251c12, ************, 7294337, 14)

It's like that Department want to add his own value to the SPS-Department term set, is it right, but why, i don't want it ^^? just need to set a string value :)
https://************-admin.sharepoint.com/_layouts/15/tenantprofileadmin/EditProperty.aspx?Name=Depa...= collapse

any idea why it's not working?

2 Replies
Strange...if Deparment property is filled in Azure AD, it should be sincronizad without any action from your side in SPO user profiles...I have just verified this on a customer tenant and it works in this way

hmmmm, so...maybe  the person who manage AD connect forgot to sync this property.... another question, in case that he forgot, can we sync specific extension property from AD Local in the azure AD department property?