Bulk UPA Profile Custom Property Update Issue

[vinay074]

We are using CSOM libraries to update SharePoint Online User profile custom property, its working fine with few 10 or 20 entries in the file, however we have entered like 500 or 1000 or more its through an error like below,

GenericError abfdeae2-2914-41b4-8724-49494eadfbea System.Data.SqlClient.SqlException (0x80131904): Input data contains multiple instances of the same single-value property for RecordId [9279448]
at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection, Action`1 wrapCloseInAction)
at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj, Boolean callerHasConnectionLock, Boolean asyncClose)
at System.Data.SqlClient.TdsParser.TryRun(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj, Boolean& dataReady)
at System.Data.SqlClient.SqlCommand.FinishExecuteReader(SqlDataReader ds, RunBehavior runBehavior, String resetOptionsString, Boolean isInternal, Boolean forDescribeParameterEncryption, Boolean shouldCacheForAlwaysEncrypted)

any clues would be appreciated.

[ToddKlindt]

This seems like a clue:
Input data contains multiple instances of the same single-value property for RecordId
Are you populating a "RecordId" field? Is it, or some other field expecting a single value but somehow you're passing it multiple values?

[AndersRask]

We need some code to be sure, but it seems more likely from the SQL exception that he is updating with a non-unique value as key (like using UPN that is not guaranteed unique, as it can be blank)

[vinay074]

we are following this doc https://docs.microsoft.com/en-us/sharepoint/dev/solution-guidance/bulk-user-profile-update-api-for-sharepoint-online#example-1 and this script looking for the idname = email id as unique value and Company name from the json file stored in the SPO.

[AndersRask]

Yeah I kinda figured that was what you were doing ;)
Could you verify in the JSON you are pulling from AAD or other, that there are no duplicates or empty values.

In PowerShell this could be done with something like

gc .\test.json -Raw | ConvertFrom-Json -Verbose | select -expand value | select idname -Unique | measure
gc .\test.json -Raw | ConvertFrom-Json -Verbose | select -expand value | select idname | measure

where test.json is your json file you have generated

The result for both lines should be the same, eg:
Count: 401
Count: 401
If they are not, you have either empty IdName (users with no emails) or non-unique emails (less likely)

If this is the case, the solution is to filter out empty values when you generate the JSON file:
Something along the lines of
$aadUsers = Get-AzureADUser -All:$true | ? UserType -eq 'Member' | ? $null -ne 'UserPrincipalName'