Work around for a bug when importing into a CDS entity

[BrianSlalomMS]

I fought this bug for weeks. Support eventually put me in touch with a developer from the MS Data Integration team who confirmed I was being bitten by a bug but showed me a work around for it.

The error occurs when rerunning an import query (called a Data Integration Project in PowerApps) and the data type of a destination field has been changed since the prior import.

The developer said the destination entity's schema is cached with the query and, when the query is rerun, it does not recognize that a destination field type has been changed and does not refresh the cache. 

In my case I changed the type of datetime field and received this error on the import attempt, 

"[Bad Request]: {"error":{"code":"0x0","message":"An error occurred while validating input parameters: Microsoft.OData.ODataException: Cannot convert the literal '' to the expected type 'Edm.DateTimeOffset'. ---> System.FormatException: The string '' is not a valid AllXsd value.".

I image other data type changes would cause different errors.

The workaround is to force the query to refresh its cache with the lastest definition of the entity and its field type definitions. You can do this in at least two different ways.

1. You can force an 'Entity Refresh' and reexecution of the query from the Data Integration Projects page using the 'Refresh project' command in the … menu. 
2. You can force just a refresh of the entites by going to the query's "Execution History" page, loping off the "/executionHistory" portion at the end of the URL, and hitting the resulting url. This will expost a page containing a "Refresh Entities" command (as well as some other commands which might be useful). There must be an easier way to get to this page but this is the method the developer showed me.

The developer said this bug should be fixed in the next release, which should be released about now, but I have not confirmed it.

[v-monli-msft]

Hi @BrianSlalomMS,

Thanks for sharing the workaround here. Hope others could benefit from it.

Regards,

Mona

[Wauters]

Thanks BrianSlalomMS

Your post helped me resolve the issue I had when exporting data from a CDS entity. I've written a small post about the workaround on how to refresh the entity cache of the PowerApps Data Integrator. It seems that the issue is still present (at least for exporting of data).

https://www.dynamict.eu/2018/12/16/missing-fields-when-exporting-data-from-powerapps-cds-for-apps/

[DerBingle]

Thanks, BrianSlalomMS, for proactively posting this, even though you already had your fix. You saved me another week of head-flattening frustration. Hard to believe Powerapps has been in production for so long, and we're still hacking URLs to work around really substantial bugs.

[BrianSlalomMS]

I don't understand how people are still hitting this bug when it was supposedly fixed in the October release. Looks like it wasn't fixed as claimed.

I'm glad you benefited from this post.

[Anonymous]

Hi,

Today i gotten this error, after changing another column from obtional to requred. I think this as the same error, even through its on another datatype. The suggested refresh work-around I cannot get to work.

Innlasting til Common Data Service mislyktes. Detaljer: ID: bd315b59-ad52-e911-a81a-000d3a463e16: Bad Request {"error":{"code":"0x0","message":"An error occurred while validating input parameters: Microsoft.OData.ODataException: Cannot convert the literal 'Nord' to the expected type 'Edm.Int32'. ---> System.FormatException: Input string was not in a correct format.\r\n at System.Number.StringToNumber(String str, NumberStyles options, NumberBuffer& number, NumberFormatInfo info, Boolean parseDecimal)\r\n at System.Number.ParseInt32(String s, NumberStyles style, NumberFormatInfo info)\r\n at System.String.System.IConvertible.ToInt32(IFormatProvider provider)\r\n at System.Convert.ChangeType(Object value, Type conversionType, IFormatProvider provider)\r\n at Microsoft.OData.ODataPayloadValueConverter.ConvertStringValue(String stringValue, Type targetType)\r\n at Microsoft.OData.ODataPayloadValueConverter.ConvertFromPayloadValue(Object value, IEdmTypeReference edmTypeReference)\r\n --- End of inner exception stack trace ---\r\n at Microsoft.OData.ODataPayloadValueConverter.ConvertFromPayloadValue(Object value, IEdmTypeReference edmTypeReference)\r\n at Microsoft.Crm.Extensibility.ODataV4.CrmPrimitivePayloadValueConverter.ConvertFromPayloadValue(Object value, IEdmTypeReference edmTypeReference)\r\n at Microsoft.OData.JsonLight.ODataJsonLightReaderUtils.ConvertValue(Object value, IEdmPrimitiveTypeReference primitiveTypeReference, ODataMessageReaderSettings messageReaderSettings, Boolean validateNullValue, String propertyName, ODataPayloadValueConverter converter)\r\n at Microsoft.OData.JsonLight.ODataJsonLightPropertyAndValueDeserializer.ReadPrimitiveValue(Boolean insideJsonObjectValue, IEdmPrimitiveTypeReference expectedValueTypeReference, Boolean validateNullValue, String propertyName)\r\n at Microsoft.OData.JsonLight.ODataJsonLightPropertyAndValueDeserializer.ReadNonEntityValueImplementation(String payloadTypeName, IEdmTypeReference expectedTypeReference, PropertyAndAnnotationCollector propertyAndAnnotationCollector, CollectionWithoutExpectedTypeValidator collectionValidator, Boolean validateNullValue, Boolean isTopLevelPropertyValue, Boolean insideComplexValue, String propertyName, Nullable`1 isDynamicProperty)\r\n at Microsoft.OData.JsonLight.ODataJsonLightResourceDeserializer.ReadEntryDataProperty(IODataJsonLightReaderResourceState resourceState, IEdmProperty edmProperty, String propertyTypeName)\r\n at Microsoft.OData.JsonLight.ODataJsonLightResourceDeserializer.ReadPropertyWithValue(IODataJsonLightReaderResourceState resourceState, String propertyName)\r\n at Microsoft.OData.JsonLight.ODataJsonLightResourceDeserializer.<>c__DisplayClass4_0.<ReadResourceContent>b__0(PropertyParsingResult propertyParsingResult, String propertyName)\r\n at Microsoft.OData.JsonLight.ODataJsonLightDeserializer.ProcessProperty(PropertyAndAnnotationCollector propertyAndAnnotationCollector, Func`2 readPropertyAnnotationValue, Action`2 handleProperty)\r\n at Microsoft.OData.JsonLight.ODataJsonLightResourceDeserializer.ReadResourceContent(IODataJsonLightReaderResourceState resourceState)\r\n at Microsoft.OData.JsonLight.ODataJsonLightReader.ReadResourceStart(PropertyAndAnnotationCollector propertyAndAnnotationCollector, SelectedPropertiesNode selectedProperties)\r\n at Microsoft.OData.JsonLight.ODataJsonLightReader.ReadAtStartImplementationSynchronously(PropertyAndAnnotationCollector propertyAndAnnotationCollector)\r\n at Microsoft.OData.ODataReaderCore.ReadImplementation()\r\n at Microsoft.OData.ODataReaderCore.InterceptException[T](Func`1 action)\r\n at System.Web.OData.Formatter.Deserialization.ODataReaderExtensions.ReadResourceOrResourceSet(ODataReader reader)\r\n at System.Web.OData.Formatter.Deserialization.ODataResourceDeserializer.Read(ODataMessageReader messageReader, Type type, ODataDeserializerContext readContext)\r\n at System.Web.OData.Formatter.ODataMediaTypeFormatter.ReadFromStream(Type type, Stream readStream, HttpContent content, IFormatterLogger formatterLogger)","innererror":{"message":"An error occurred while validating input parameters: Microsoft.OData.ODataException: Cannot convert the literal 'Nord' to the expected type 'Edm.Int32'. ---> System.FormatException: Input string was not in a correct format.\r\n at System.Number.StringToNumber(String str, NumberStyles options, NumberBuffer& number, NumberFormatInfo info, Boolean parseDecimal)\r\n at System.Number.ParseInt32(String s, NumberStyles style, NumberFormatInfo

info)\r\n at System.String.System.IConvertible.ToInt32(IFormatProvider provider)\r\n at System.Convert.ChangeType(Object value, Type conversionType, IFormatProvider provider)\r\n at Microsoft.OData.ODataPayloadValueConverter.ConvertStringValue(String stringValue, Type targetType)\r\n at Microsoft.OData.ODataPayloadValueConverter.ConvertFromPayloadValue(Object value, IEdmTypeReference edmTypeReference)\r\n --- End of inner exception stack trace ---\r\n at Microsoft.OData.ODataPayloadValueConverter.ConvertFromPayloadValue(Object value, IEdmTypeReference edmTypeReference)\r\n at Microsoft.Crm.Extensibility.ODataV4.CrmPrimitivePayloadValueConverter.ConvertFromPayloadValue(Object value, IEdmTypeReference edmTypeReference)\r\n at Microsoft.OData.JsonLight.ODataJsonLightReaderUtils.ConvertValue(Object value, IEdmPrimitiveTypeReference primitiveTypeReference, ODataMessageReaderSettings messageReaderSettings, Boolean validateNullValue, String propertyName, ODataPayloadValueConverter converter)\r\n at Microsoft.OData.JsonLight.ODataJsonLightPropertyAndValueDeserializer.ReadPrimitiveValue(Boolean insideJsonObjectValue, IEdmPrimitiveTypeReference expectedValueTypeReference, Boolean validateNullValue, String propertyName)\r\n at Microsoft.OData.JsonLight.ODataJsonLightPropertyAndValueDeserializer.ReadNonEntityValueImplementation(String payloadTypeName, IEdmTypeReference expectedTypeReference, PropertyAndAnnotationCollector propertyAndAnnotationCollector, CollectionWithoutExpectedTypeValidator collectionValidator, Boolean validateNullValue, Boolean isTopLevelPropertyValue, Boolean insideComplexValue, String propertyName, Nullable`1 isDynamicProperty)\r\n at Microsoft.OData.JsonLight.ODataJsonLightResourceDeserializer.ReadEntryDataProperty(IODataJsonLightReaderResourceState resourceState, IEdmProperty edmProperty, String propertyTypeName)\r\n at Microsoft.OData.JsonLight.ODataJsonLightResourceDeserializer.ReadPropertyWithValue(IODataJsonLightReaderResourceState resourceState, String propertyName)\r\n at Microsoft.OData.JsonLight.ODataJsonLightResourceDeserializer.<>c__DisplayClass4_0.<ReadResourceContent>b__0(PropertyParsingResult propertyParsingResult, String propertyName)\r\n at Microsoft.OData.JsonLight.ODataJsonLightDeserializer.ProcessProperty(PropertyAndAnnotationCollector propertyAndAnnotationCollector, Func`2 readPropertyAnnotationValue, Action`2 handleProperty)\r\n at Microsoft.OData.JsonLight.ODataJsonLightResourceDeserializer.ReadResourceContent(IODataJsonLightReaderResourceState resourceState)\r\n at Microsoft.OData.JsonLight.ODataJsonLightReader.ReadResourceStart(PropertyAndAnnotationCollector propertyAndAnnotationCollector, SelectedPropertiesNode selectedProperties)\r\n at Microsoft.OData.JsonLight.ODataJsonLightReader.ReadAtStartImplementationSynchronously(PropertyAndAnnotationCollector propertyAndAnnotationCollector)\r\n at Microsoft.OData.ODataReaderCore.ReadImplementation()\r\n at Microsoft.OData.ODataReaderCore.InterceptException[T](Func`1 action)\r\n at System.Web.OData.Formatter.Deserialization.ODataReaderExtensions.ReadResourceOrResourceSet(ODataReader reader)\r\n at System.Web.OData.Formatter.Deserialization.ODataResourceDeserializer.Read(ODataMessageReader messageReader, Type type, ODataDeserializerContext readContext)\r\n at System.Web.OData.Formatter.ODataMediaTypeFormatter.ReadFromStream(Type type, Stream readStream, HttpContent content, IFormatterLogger formatterLogger)","type":"Microsoft.Crm.CrmHttpException","stacktrace":" at Microsoft.Crm.Extensibility.OData.CrmODataUtilities.ValidateInputParameters(ModelStateDictionary controllerModelState)\r\n at Microsoft.Crm.Extensibility.OData.EntityController.PatchEntityImplementation(String& entityName, String key, EdmEntityObject entityDelta)\r\n at Microsoft.PowerApps.CoreFramework.ActivityLoggerExtensions.Execute[TResult](ILogger logger, EventId eventId, ActivityType activityType, Func`1 func, IEnumerable`1 additionalCustomProperties)\r\n at Microsoft.Xrm.Telemetry.XrmTelemetryExtensions.Execute[TResult](ILogger logger, XrmTelemetryActivityType activityType, Func`1 func)\r\n at lambda_method(Closure , Object , Object[] )\r\n at System.Web.Http.Controllers.ReflectedHttpActionDescriptor.ActionExecutor.<>c__DisplayClass10.<GetExecutor>b__9(Object instance, Object[] methodParameters)\r\n at System.Web.Http.Controllers.ReflectedHttpActionDescriptor.ExecuteAsync(HttpControllerContext controllerContext, IDictionary`2 arguments, CancellationToken cancellationToken)\r\n--- End of stack trace from previous location where exception was thrown ---\r\n at System.Runtime.ExceptionServices.ExceptionDispatchInfo.Throw()\r\n at System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task)\r\n at System.Web.Http.Controllers.ApiControllerActionInvoker.<InvokeActionAsyncCore>d__0.MoveNext()\r\n--- End of stack trace from previous location where exception was thrown ---\r\n at System.Runtime.ExceptionServices.ExceptionDispatchInfo.Throw()\r\n at System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task)\r\n at System.Web.Http.Controllers.ActionFilterResult.<ExecuteAsync>d__2.MoveNext()\r\n--- End of stack trace from previous location where exception was thrown ---\r\n at System.Runtime.ExceptionServices.ExceptionDispatchInfo.Throw()\r\n at System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task)\r\n at System.Web.Http.Dispatcher.HttpControllerDispatcher.<SendAsync>d__1.MoveNext()"}}}. (Jobb-ID: d0fc19d4-4144-437b-8498-4e259596ff2d)

Kind Regards Dan

[Caroline]

@Anonymous I have also run into the same bug whenchanging a column from decimal to whole number. Did you find a solution?

[Anonymous]

Hello @Wauters ,  @BrianSlalomMS BrianSlalomMS

Thanks for your post about the workaround. I followed the steps and refreshed the data entity from the recently created DI project but the bug still appear when I try to create a new project which is as follows. Could you please help me to get through this error? I have successfully created a connection sets and on successful execution of this project, I could accomplish DI integartion from CDS to F&O hopefully.

[(([Table with name 'cdm_jobfunctions' does not exist], The remote server returned an error: (404) Not Found.), Unable to retrieve metadata for CRM environment: https://uk-001.azure-apim.net/apim/commondataservice/e34373ab-75e1-4173-8c76-daf4a9fcb523/$metadata.... [Table with name 'cdm_jobfunctions' does not exist], [{ "status": 404, "message": "Table with name 'cdm_jobfunctions' does not exist", "error": { "message": "Table with name 'cdm_jobfunctions' does not exist" }, "source": "commondataservice-uks.logic-ase-uksouth.p.azurewebsites.net", "debugInfo": "clientRequestId: 827f810c-d829-453e-a8c0-68be88fab1ea" }], The remote server returned an error: (404) Not Found.)], One or more errors occurred.
Session ID: 1782dc76-4c1c-4bee-aaa1-9db58789ca4e
Root Activity ID: 39c6b2bb-43f4-4d01-a51b-084659b06421