cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
27pchrisl
Regular Visitor

Importing OData DateTime fields results in CrmDateTime error

Hi,

 

When importing a OData v4 compliant data feed that includes DateTime or DateTimeOffset properties as a Dataflow, import errors are received such as: 

"Reason: Bad Request, Error code: 0x80040239, Message: DateTime is less than minumum value supported by CrmDateTime. Actual value: 01/01/0001 00:00:00, Minimum value supported: 01/01/1753 00:00:00"

 

This can be reproduced by using the Northwind reference V4 source from the OData specification:

https://services.odata.org/V4/Northwind/Northwind.svc/

 

Process to reproduce:

Create new Dataflow (with any name)

Use "https://services.odata.org/V4/Northwind/Northwind.svc/" as the source, click "Next"

Choose the "Employees" endpoint. Note that the data loads correctly into PowerQuery, and click "Transform Data"

Click "Next" on the transform data screen

Choose "Load to new entity" on the Map entities screen, leaving all the defaults in place and choose Next

Click "Create" to create the import

 

After a few moments, observe that the data feed import has failed. There does not appear to be any workaround for this problem on the OData side.

7 REPLIES 7
danvarga
Microsoft
Microsoft

Hi @27pchrisl This looks like a permission problem. I have 2 environments for testing it. My personal environment I'm the administrator and I was able to follow the steps you provided without any issues. Data Flow has been created successfully.

 

danvarga_0-1596824098061.png

In the second environment I'm not the owner or the administrator and I'm getting this issue after few seconds:

 

danvarga_1-1596824162163.png

Issue details:

There was an error while creating this entity. Details: Failed to create entity with logical name cr38e_employees and object type code -1. Exception: Microsoft.Crm.CrmSecurityException: SecLib::CheckPrivilege failed. User: 99324e48-7394-ea11-a811-000d3a579806, PrivilegeName: prvCreateEntity, PrivilegeId: 341e3ebf-74b8-4335-84f3-7f617bb7d081, Required Depth: Basic, BusinessUnitId: a47f0694-a911-e911-a98f-000d3a1c9b20, MetadataCache Privileges Count: 1731, User Privileges Count: 692 at Microsoft.Crm.BusinessEntities.SecurityLibrary.ThrowPrivilegeDeniedException(IUser user, RolePrivilege rolePrivilege, ExecutionContext context) at Microsoft.Crm.BusinessEntities.SecurityLibrary.GetMaxPrivilegeDepthForUserAcrossBusinessUnitsInternal(Guid user, Guid privilege, ExecutionContext context, Guid& businessUnitId) at Microsoft.Crm.BusinessEntities.SecurityLibrary.CheckPrivilegeGlobalDepth(Guid user, Guid privilege, ExecutionContext context) at Microsoft.Crm.Metadata.EntityService.<>c__DisplayClass35_0.<CreateInternalHelper>b__1() at Microsoft.Crm.SqlTelemetryHelper.LogSqlTimes(Action action, String operationName) at Microsoft.Crm.Metadata.EntityService.CreateInternalHelper(EntityCreateInfo entityInfo, MetadataHelper metadataHelper, ExecutionContext context). [Entity: Employees]

 

I'm not getting any issue related to the date.

Hi @danvarga, appreciate your looking into this!

 

Can you confirm that your Dataflow imports successfully, and in the CSV log generated in the "Refresh history" after import there are no errors, and that the CSV indicates that 9 records were correctly upserted?

 

I get 2 records upserted, and 7 non-importable with the error message I provided previously. I have attached a screenshot of the two import runs on this particular flow (both showing the same errors), and the CSV I got back from the import log (converted to xlsx, since I cannot attach CSV here).

 

Thanks!

 

 - Chris

@27pchrisl I'm not sure if I'm missing any step, but everything looks fine for me.

 

danvarga_0-1596826628418.png

 

danvarga_1-1596826782606.png

 

 

LooseChippings
Advocate II
Advocate II

Hi @27pchrisl

 

We are based in the UK and have encountered the same problem with Dataflows/Power Query and dates not in the US format.  The work around that solves the problem for us is, in Power Query, to change Options -> Project Options ->Locale from English (United Kingdom) to English (United States).

 

It doesn't make logical sense, but it solves the issue.  If you are in the US or using US format dates, please ignore this suggestion. 

I should add that it is dates where the first characters in UK format (Day) are greater than 12. So, 29/12/2019 fails, but 08/07/2020 is OK because Dataflow thinks 08 is the month.

Hi,

 

Yes, changing the Locale to US does cause the import to succeed. So that's good news! Thanks @LooseChippings 

 

I believe it points to an issue somewhere downstream then, since OData DateTimeOffsets are always in ISO8601 format and don't change format based on locale. It would look like it's a problem with the service that PowerQuery is pushing into, since PQ itself does load the dates correctly as "Date/Time/Zone". PowerBI also has no issue with the feed when using that on the desktop.

 

https://services.odata.org/V4/Northwind/Northwind.svc/Employees?$select=HireDate&$format=application...

 

Is that your understanding as well?

 

Cheers,

 

 - Chris

 

Yes. It is not OData and occurs when using any Dataflow connector.  This is not new.  We first encountered the problem around three years ago.

 

We have British Summer Time, when the clocks go back one hour between March and October, and PowerBI does not recognise this change.  So 2020-08-07 00:00:00 becomes 2020-08-06 11:00:00 in Power BI.  This is also a known issue and we add one hour to dates in Power Query.

 

 

Helpful resources

Announcements
PA_User Group Leader_768x460.jpg

Manage your user group events

Check out the News & Announcements to learn more.

Community Connections 768x460.jpg

Community & How To Videos

Check out the new Power Platform Community Connections gallery!

Welcome Super Users.jpg

Super User Season 2

Congratulations, the new Super User Season 2 for 2021 has started!

Carousel 2021 Release Wave 2 Plan 768x460.jpg

2021 Release Wave 2 Plan

Power Platform release plan for the 2021 release wave 2 describes all new features releasing from October 2021 through March 2022.

Top Solution Authors
Top Kudoed Authors
Users online (959)