Forum Discussion
sftp file modified date
Hei Having a hard time picking up the modified datetime of files from a sftp server. With a Get MEtadata activity, the Last Modified field actually is just the time when I run that activity - so t...
jubinsoni
Memorable Member
Memorable MemberHi 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
| Step | Activity | What it does |
|---|---|---|
| 1 | Lookup on a watermark table | Reads the last processed modified timestamp for this source |
| 2 | Get Metadata on the folder, field list Child items | Returns the list of file names |
| 3 | ForEach over the child items | Runs once per file |
| 4 | Get Metadata inside the loop, using the file name, field list Item name and Last modified | Gets the real last modified value for that file |
| 5 | If Condition comparing last modified with the watermark | Only continues for files newer than the watermark |
| 6 | Copy activity for the new file | Loads it into the Lakehouse or Warehouse |
| 7 | After the loop, update the watermark | Stores the largest last modified value that was processed successfully |
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
| Topic | Recommendation |
|---|---|
| Watermark update timing | Update it only after the loop finished without failure, otherwise a failed run can skip files that were never loaded |
| Files written while you read | Files still being uploaded can have a recent modified time; use an age buffer (only take files older than a few minutes) or a done marker file |
| Same timestamp on several files | Use greater than or equal and remember which files were processed, or keep a log table of loaded file names |
| Time zone | Compare timestamps in one time zone; the server and the pipeline may differ |
| Large folders | A Get Metadata call per file is fine for hundreds of files; for many thousands, consider a copy activity setting that filters by last modified time directly on the source, if your connector supports it, and compare the options in the documentation |
| Re-processing | Keep a log table of file name, modified time and load time, so a file can be reloaded on purpose |
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 timestampsAI-assisted drafting: AI was used to help structure and phrase this response. I reviewed and validated the technical content before posting.