Azure Data Factory Get First Last Modified File

Question

Azure Data Factory Get First Last Modified File

Jay 506

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.

KranthiPakala-MSFT 36,022 Reputation points Microsoft Employee

2021-04-07T19:15:23.433+00:00

Hi @Jay ,

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
Jay 506 Reputation points

2021-04-08T07:42:57.547+00:00

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

If I add Last Modified

I then get an error

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

KranthiPakala-MSFT 36,022 Reputation points Microsoft Employee

2021-04-07T19:15:23.433+00:00

Hi @Jay ,

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
Jay 506 Reputation points

2021-04-08T07:42:57.547+00:00

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

If I add Last Modified

I then get an error

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

Answer 1

Hi @Jay ,

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.

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.

Dervishi, Erald 66 Reputation points

2022-08-22T16:46:43.613+00:00

Hi @KranthiPakala-MSFT
I had a similar project scenario as above and following your recommendations it worked picking up the latest file.
But I have another addition to it how to pick up the latest file with a specific string in the fileName for example.

fileGood - modified date 01-JAN-2.csv
fileGood - modified date 17-JAN-21.csv
fileNotGood - modified date 17-JAN-21.csv
fileNotGood - modified date 17-JAN-21.csv

I would like to pick up fileGood - modified date*.csv

Please let me know of any recommendations.
Thank you in advance

Answer 2

Jay 506

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

KranthiPakala-MSFT 36,022 Reputation points Microsoft Employee

2021-04-12T16:58:42.8+00:00
Hi @Jay ,

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.

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

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

----------
KranthiPakala-MSFT 36,022 Reputation points Microsoft Employee

2021-04-13T17:11:01.89+00:00

Hi @Jay ,

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

Azure Data Factory Get First Last Modified File

1 additional answer

Activity