sftp file modified date

Hi,

Two things to check. If you run Get Metadata on the folder, you only get the folder’s timestamp, and some SFTP servers report that as the current time. Instead, get the childItems, loop over them with a ForEach, and run Get Metadata on each file to read its own Last Modified.

The easier route is to skip it and use the Copy activity’s “Filter by last modified” (start and end time) on the SFTP source. Store the last run time in a watermark table or pipeline variable and pass it in, so only new files get picked up.

If this helped, feel free to give it a kudos or mark it as solution, helps others find it too.

Hi vladenache​, you should be able to solve with the following steps in the pipeline,

1. Add Get Metadata activity on the folder and select field list Child items.
2. Add a ForEach using @activity('Get Metadata1').output.childItems.
3. Inside the foreach loop, add a second Get Metadata activity and use @item().name in the file path. In the field list select Item name and Last modified.

,

Hi vladenache​,

The approach above will let you retrieve the actual file-level Last modified value. I would add one more step if your end goal is incremental pickup rather than only displaying that timestamp.

A practical pattern is:

• Get the folder child items.
• ForEach file, retrieve its lastModified metadata.
• Compare that value against a persisted watermark representing the last successfully processed timestamp.
• Process only files newer than that watermark.
• Update the watermark only after the pipeline completes successfully.

Alternatively, if the requirement is simply to copy files modified after a known timestamp, the Fabric SFTP Copy activity already supports Filter by last modified. You can provide a UTC Start time so that only files whose modified timestamp is greater than or equal to that value are selected.

I would avoid using utcNow() as the watermark at the start of every run, because a failed or long-running pipeline could then create gaps. Persisting the maximum successfully processed lastModified value is generally safer.

Also keep in mind that childItems itself only returns the child item name/type, so you still need the second Get Metadata activity if you specifically want each individual file's lastModified value.

AI-assisted drafting: AI was used to help structure and phrase this response. I reviewed and validated the technical content before posting.

Hi vladenache​,

Echoing nielsvdc​ and ShivekMaharaj​: the folder-level metadata call returns only the child items, so the real last modified value comes from a second Get Metadata call per file inside a ForEach. Since your end goal is picking up only new files, here is the complete incremental pattern around that, with the choices you will need to make.

The pattern, step by step

The condition inside the loop

@and(
    greater(activity('Get File Metadata').output.lastModified, variables('LastWatermark')),
    endswith(item().name, '.csv')
)

The activity name and the variable name are placeholders for whatever you call them in your pipeline.

Choices that matter

With a log table you also get an audit trail for free, which is helpful when someone asks why a file did not appear.

So my overall approach would be:

1. Keep a watermark (and ideally a file log) in a control table
2. List files, then get each file's real last modified inside a ForEach
3. Copy only files newer than the watermark, with a small age buffer
4. Update the watermark after a fully successful run
5. Compare time zones and handle equal timestamps

AI-assisted drafting: AI was used to help structure and phrase this response. I reviewed and validated the technical content before posting.

hei everyone, I appreciate all your answers, but this doesn't seem to work for me.
when I run a Get Metadata activity with a specific file name, and retreive the last modified datetime, it returns the actual time when the activity was run. like in this example. the example file has a hardcoded timestamp in the name to show when it was produced, but look at the last modified value i get, it is exactly UTC time when i ran the activity:

So I know for a fact the file was uploaded on the 10th of november, not the minute I ran the activity.

As a clarification, not all files have a hardcoded datetime in their name, so I cannot rely on extracting the actual value from the item name alone.

Thanks

vladenache​  One detail that can save you the per-file Get Metadata calls: Get Metadata can filter by last modified itself. Set modifiedDatetimeStart and modifiedDatetimeEnd in its store settings, and childItems then returns only the files modified in that range. The filter works only when Get Metadata points at a folder, not a file. Times are UTC, and files in subfolders aren't included.
1. Point Get Metadata at the folder with field list Child items, and set the window. In the activity JSON it sits here:

"storeSettings": {
    "type": "SftpReadSettings",
    "modifiedDatetimeStart": "@{variables('LastWatermark')}",
    "modifiedDatetimeEnd": "@{pipeline().TriggerTime}"
}

2. Feed childItems into the ForEach and Copy, so only new files reach the loop.
3. After a fully successful run, store the same TriggerTime as the new watermark. The window is start inclusive and end exclusive, so consecutive runs neither skip nor repeat a file.
4. If you filter in the Copy activity instead, note that the last modified filter is ignored when File path type is List of files.
If a file-level Get Metadata still returns the run time, compare the same file's timestamp in an SFTP client such as WinSCP. Both Get Metadata and the Copy filter read the Last Modified attribute the server reports. If the server doesn't report a real one, neither filter will work, and a log table of processed file names (or a date in the file name) is the fallback.
Note: this filter is documented on the Azure Data Factory Get Metadata page. The Fabric Get Metadata page doesn't mention it, so test it once on a small folder.
If this solves it, please mark it as the solution.

References:
https://learn.microsoft.com/en-us/azure/data-factory/control-flow-get-metadata-activityhttps://learn.microsoft.com/en-us/fabric/data-factory/connector-sftp-copy-activity 

hei ShafiUllah-bc​ , thanks for the message.

if you understand correctly, you propose that the trigger time should be saved as a watermark - however, my idea is that i should rely on the actual modified time of the file from the sftp server, not the time i trigger a run. thus, i still need a succesfull way of accessing that particular file atribute through fabric