topic Re: ADF OData Connection Parameters in Pipelines

ADF OData Connection Parameters

BrianEllul — Fri, 16 Aug 2024 12:29:24 GMT

I am querying data from a Dynamics365 Business Central. I'm passing the Company as the path and that works well however now I want to filter more the data by passing a 'Last Modified Date Time' > ???

Trying to use the Query, however no errors but my parameter is ignored.

Any pointers please?

Regards

Brian

Re: ADF OData Connection Parameters

rubayatyasmin — Fri, 16 Aug 2024 22:06:10 GMT

Hi,

You mentioned ADF, so there should be a last modified date option in the pipeline. I did this a couple of months ago. I was getting data that are 1 day old. In my scenario, I was getting data export. so on aug 19th I'd get export for aug18th data and so forth. so, I used this code below, it works just fine.

Re: ADF OData Connection Parameters

rubayatyasmin — Fri, 16 Aug 2024 22:21:24 GMT

Alos, this thread might help

https://community.dynamics.com/forums/thread/details/?threadid=76bbaed2-31b4-ee11-a569-6045bda7c23b

make sure the column name is correct. you can also check this one,

https://api.businesscentral.dynamics.com/v2.0/{tenant}/sandbox/ODataV4/Company('YourCompany')/YourEntity?$filter=LastModifiedDateTime gt 2023-08-16T00:00:00Z

fill the info

Re: ADF OData Connection Parameters

Anonymous — Mon, 19 Aug 2024 01:05:34 GMT

Hi BrianEllul

Thank you very much rubayatyasmin for your prompt reply.

Can you tell me if your problem is solved? If yes, please accept it as solution.

This forum is used to discuss Fabric related content.

Since this is a product issue beyond the scope of this forum, if you still have questions, I recommend that you post this query on the Azure Data Factory Community Forum.

The following links:

Azure Data Factory - Microsoft Community Hub

If you have any questions related to fabrics, please continue to use this forum to contact us!

Regards,

Nono Chen

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Re: ADF OData Connection Parameters

BEllul — Mon, 26 Aug 2024 19:40:11 GMT

Thank you for your feedback, I am using OData source to connect to the business central and download the data. To the "Path", I am passing these parameters: Company('mycompanyname')/Dimension_Values_Excel?$Filter=Last_Modified_Date_Time gt 2024-08-23T10:00:00.00Z

This, doesn't load any errors, however, it loads data from May 2024, it seems that the Last_Modified_Date_Time is being ignored.

Re: ADF OData Connection Parameters

lbendlin — Mon, 26 Aug 2024 19:42:14 GMT

i would think that stuff is case sensitive?

Query options overview - OData | Microsoft Learn

Re: ADF OData Connection Parameters

BEllul — Fri, 30 Aug 2024 13:13:12 GMT

Hi Everyone and thanks for your input

Back to this issue....

The error I'm now receiving is then below. Again incorrect datetime format!

Requesting response from path Company('xxxxxxxx')/Dimension_Values_Excel?$Filter=Last_Modified_Date_Time gt 2024-08-23T10:00:00.00Z and query $top=10 failed with status code BadRequest and message {"error":{"code":"BadRequest","message":"The DateTimeOffset text '2024-08-23T10:00:00.00Z?$top=10' should be in format 'yyyy-mm-ddThh:mm:ss('.'s+)?(zzzzzz)?' and each field value is within valid range. CorrelationId: 9b43a6ca-1d12-413c-bfcf-a3a9eefed6a0."}}.

Not sure what is wrong with this format - 2024-08-23T10:00:00.00Z

'It's expecting this format - yyyy-mm-ddThh:mm:ss('.'s+)?(zzzzzz)? Not sure what the trailing part is tbh

Regards

Brian

Re: ADF OData Connection Parameters

BEllul — Fri, 30 Aug 2024 13:24:57 GMT

Hi Again

I found this date time format which I tried passing - 2017-01-02 00:00:00.0000000

Still same error!!

Re: ADF OData Connection Parameters

lbendlin — Fri, 30 Aug 2024 13:26:58 GMT

You seem to have an extra question mark

?$top=10

replace it with &

Re: ADF OData Connection Parameters

BEllul — Fri, 30 Aug 2024 13:40:51 GMT

Hello

Thanks a lot for your reply.

This is what I'm actually passing - Company('xxxxxxxx')/Dimension_Values_Excel?$Filter=Last_Modified_Date_Time gt 2024-08-23T10:00:00.00Z

The rest has been added by the Data Factory itslef since I'm pressing the Preview Data and it's seems that preview will give me the top 10 rows, so unfortunately I don't have control on this part - ?$top=10 since it has been appended automatically.

Reading your comment, I decided to skip the data preview function and run the pipeline with my parameters directly. Again I pasted a format which I found online.

Company('xxxxxxxxxxxx')/Dimension_Values_Excel?$Filter=Last_Modified_Date_Time gt 2024-08-28T00:00:00.0000000Z

This time the top was not added however it's still complaining of a sytax error!

Failure happened on 'Source' side. ErrorCode=ODataRequestNotSucceeded,'Type=Microsoft.DataTransfer.Common.Shared.HybridDeliveryException,Message=Requesting response from path Company('COME%20ON%20MALTA%20LIMITED')/Dimension_Values_Excel?$Filter=Last_Modified_Date_Time gt 2017-01-02 00:00:00.0000000Z and query failed with status code BadRequest and message {"error":{"code":"BadRequest","message":"Syntax error at position 40 in 'Last_Modified_Date_Time gt 2017-01-02 00:00:00.0000000Z'. CorrelationId: fad854b9-7cee-4327-80a8-8a7133ce5b4b."}}.,Source=Microsoft.DataTransfer.Runtime.ODataConnector,'

The below is the previous format I was using... just a difference at the trailing part

Company('xxxxxxxx')/Dimension_Values_Excel?$Filter=Last_Modified_Date_Time gt 2024-08-23T10:00:00.00Z

Regards

Brian

Re: ADF OData Connection Parameters

lbendlin — Fri, 30 Aug 2024 14:03:29 GMT

It seems to be swallowing the "T" that is mandatory for ISO8601.