question

Jay-8106 avatar image
0 Votes"
Jay-8106 asked KranthiPakala-MSFT commented

Azure Data Factory Get First Last Modified File

Hi,

I have a GetMetadata Activity returning Arguments: Child Items and Last Modified,
is there an easy way to add a filter step which just says give me the first Modified Date File?

e.g. if GetMetadata step returns three files:

file1.xml - modified date 17-JAN-21
file2.xml - modified date 01-JAN-21
file3.xml - modified date 24-JAN-21

I only want the filter to return:

file2.xml - modified date 01-JAN-21

Thanks.

azure-data-factory
· 2
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Hi @Jay-8106,

Thanks for reaching out. I was trying to create a pipeline to achieve your requirement, but noticed that the Last Modified argument returns the last modified date of the folder when you point your dataset to a folder. And Child Items argument return name and type of the file and doesn't return the Last Modified date of the file. Are you having a GetMetadata activity and passing output to a ForEach activity and having another GetMetadata activtiy inside it to get the last modified date of each file?

Appreciate if you could share your pipeline structure so that it would help us to reproduce and assist better on this requirement.

Thanks

0 Votes 0 ·

Hi KranthiPakala,

Thanks for trying this but I have just tried a test and don't think I can do this in Azure as Azure can;t get the Last Modified from a remote server.

My Test, I can select the files, see image

85596-image1.png


If I add Last Modified

85559-image2.png

I then get an error

85560-image3.png

Pipeline Run ID: 43665633-809a-4635-8529-c0214448cad0

0 Votes 0 ·
image1.png (19.3 KiB)
image2.png (16.4 KiB)
image3.png (19.0 KiB)
KranthiPakala-MSFT avatar image
1 Vote"
KranthiPakala-MSFT answered

Hi @Jay-8106 ,

Thanks for your response. In order to copy the First Last modified file, you can follow the same steps described in this thread: ADF: copy last modified blob
But in the If condition activity, instead of @greater() function you will have to use @less() function since you want to copy the first Last modified in other terms first modified file.

And also for the pipeline reference date variable you will have to assign the pipeline trigger time varReferenceDateTime = @pipeline().TriggerTime instead of varReferenceDateTime = 1900-01-01 00:00:00.

86406-image.png

This implementation should help you achieve your requirement.

Do let us know how if it goes



Please don’t forget to Accept Answer and Up-Vote wherever the information provided helps you, this can be beneficial to other community members.



image.png (157.7 KiB)
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Jay-8106 avatar image
0 Votes"
Jay-8106 answered KranthiPakala-MSFT commented

Corey Blimey that's a lot of steps to get the first or last file :-)
Thanks KranthiPakala I'll take a look.

Failing this I guess my next option would be to use filename which contains the file created date and time:

2021-04-08_05-03-16.xml
2021-04-08_03-01-10.xml

I.e. strip this out and write to CosmosDB and then Select out with an Order By FileCreatedDateTime desc TOP 1 then use this as a variable to select the file,
none of it sounds straight forward.

Would be nice to have in ADF a Metadata Sort Task which you could place after current Get Metadata to be able to select a Get Metadata field and Sort asc or desc and return Top x i.e. Top 1 or 10 etc.

Thanks,
J

· 2
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Hi @jay-8106,

Thanks for your response. Currently the above implementation is one way to achieve your requirement. Another way I could think of is to use Custom activity or a Azure function activity to write your own sorting logic to achieve the desired result.

But agree with your feedback. Below are couple of already existing feedback on the same from ADF user voice forum. I would encourage you to please up-vote and/or comment on these feedback items which would help to increase the priority of the feature implementation.

  1. https://feedback.azure.com/forums/270578-data-factory/suggestions/37652935-adf-getmetadata-sort-functionality

  2. https://feedback.azure.com/forums/270578-data-factory/suggestions/41826307-customized-ordering-of-get-metadata-activity-inste

Also if you have any additional feedback please feel free to log a new request in the user voice forum.

Thank you







0 Votes 0 ·

Hi @jay-8106,

Just checking in to see if the above suggestion was helpful. If this answers your query, please do click “Accept Answer” and/or Up-Vote, as it might be beneficial to other community members reading this thread.

And, if you have any further query do let us know.

Thank you

0 Votes 0 ·