New-PnPUPABulkImportJob fails to import json string data into SharePointOnline 'date no year' fields #1653
Unanswered
1AlexBaker
asked this question in
General
Replies: 0 comments
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment
-
We are loading multiple field updates into SharePoint Online User Profiles using the bulk import job processing (due to attributes not being synchronized from else where). We have run into an issue with one field that appears like it may be running afoul of a SQL ODATA import bug within SharePoint Online or with the New-PnPUPABulkImportJob command.
In SharePoint Online, the custom attribute field is set 'date no year'.
We are exporting and creating the json text to load via PowerShell 7 on Server 2019 with PnP.PowerShell loaded.
Other fields are loading fine but we get an error for this one attribute from SharePoint Online no matter what format we try for this field (MM/dd, M-dd, M-d, month-day, month/day, month day).
Here is the error:
2/25/2022 10:49:27 PM GenericError a9e68f49-aa7c-459d-a1e5-8d19858f6c16 System.Data.SqlClient.SqlException (0x80131904): Conversion failed when converting date and/or time from character string.
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) at System.Data.SqlClient.SqlCommand.RunExecuteReaderTds(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, Boolean async, Int32 timeout, Task& task, Boolean asyncWrite, Boolean inRetry, SqlDataReader ds, Boolean describeParameterEncryptionRequest) at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method, TaskCompletionSource
1 completion, Int32 timeout, Task& task, Boolean& usedCache, Boolean asyncWrite, Boolean inRetry)at System.Data.SqlClient.SqlCommand.InternalExecuteNonQuery(TaskCompletionSource
1 completion, String methodName, Boolean sendToPipe, Int32 timeout, Boolean& usedCache, Boolean asyncWrite, Boolean inRetry) at System.Data.SqlClient.SqlCommand.ExecuteNonQuery() at Microsoft.Office.Server.Data.SqlSession.ExecuteNonQuery(SqlCommand command, SqlInfoMessageEventHandler handler) at Microsoft.Office.Server.Data.SqlSession.ExecuteNonQuery(SqlCommand command) at Microsoft.Office.Server.Directory.SharePoint.Provider.DirectoryStorageManager.ProcessUserChanges(IEnumerable
1 changes)at Microsoft.Office.Server.Directory.SharePoint.Provider.UserC
Here is a sample of the script showing how we are handling loading the data from the existing AD objects (we have a much longer string of attributes loading successfully but here is a sample of how the script works for a single attribute):$null) -or ($ .attributeX -eq "null")) { "" } else { (get-date $.attributeX -Uformat "%M/%d") }};L="attributeX"}
--script start--
$SiteUrl = "https://yoursite.sharepoint.com"
$SiteRelativeFolderPath = "/Shared Documents"
$SiteAdminUrl = "https://yoursite-admin.sharepoint.com"
$FetchFile = "https://yoursite.sharepoint.com/Shared Documents/test.json"
$JsonData = get-aduser -Filter { (Enabled -eq $true) -and ((SamAccountName -eq 'xxxx') -or (SamAccountName -eq 'yyyy')) } -SearchBase "OU=abc,DC=def,DC=ghi,DC=com" -Properties UserPrincipalName,Name,attributeX | Select-Object -Property UserPrincipalName,Name,attributeX | where { !($.Name.StartsWith("donotincludeme")) } | Select-object@{E={ IF(($.attributeX -eq
$JSON = @"
{ "value":
$JsonData
}
"@
$JSON | Out-File -Encoding UTF8 -FilePath "c:\test\test.json" -Force
Add-PnPFile -Path "c:\test\test.json" -Folder $SiteRelativeFolderPath
New-PnPUPABulkImportJob -Url $FetchFile -IdProperty "UserPrincipalName" -IdType PrincipalName -UserProfilePropertyMapping "attributeX"="attributeX"}
--script end--
Does this qualify as a bug or is there something special about how to load a 'date no year' field into SharePoint Online using PnP.PowerShell?
Beta Was this translation helpful? Give feedback.
All reactions