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
Power Apps News & Annoucements carousel

Power Apps News & Announcements

Keep up to date with current events and community announcements in the Power Apps community.

Microsoft 365 Conference – December 6-8, 2022

Microsoft 365 Conference – December 6-8, 2022

Join us in Las Vegas to experience community, incredible learning opportunities, and connections that will help grow skills, know-how, and more.

Power Apps Community Blog Carousel

Power Apps Community Blog

Check out the latest Community Blog from the community!

Top Solution Authors
Top Kudoed Authors
Users online (5,448)