How to bypass locked temporary Office files (~$*) during bulk FileServer-to-ADLS copy in ADF without high cost?

Question

How to bypass locked temporary Office files (~$*) during bulk FileServer-to-ADLS copy in ADF without high cost?

Misbah Bin Hossain 0

Hi Everyone,

I am building an incremental ingestion pipeline in Azure Data Factory (ADF) to copy ~70,000 files (across 47 different file types) from an on-premises FileServer into ADLS Gen2 using a Self-Hosted Integration Runtime (SHIR).

The Challenge

We need to exclude temporary files (.tmp, .db) and Microsoft Office temporary lock files (~$*, e.g., ~$Project Plan.xlsx).

When ADF executes a Copy Activity with wildcardFileName: "*.*", it hits open ~$* files and fails immediately with an OS-level SMB Sharing Violation / File in Use exception.

Approaches Attempted & Technical Blockers

skipErrorFile / Fault Tolerance in Copy Activity: Binary datasets do not support row-level skipping, and file-level skipping fails because the lock occurs at the SMB directory enumeration level before binary streaming starts.

ADF Mapping Data Flow: The FileServer connector is not supported over SHIR in Mapping Data Flow, so direct stream filtering at the source is unavailable.

Get Metadata + ForEach Loop: Get Metadata fails due to the 5,000 child item limit on directories. Traversing 70k files via ForEach hits the 50,000 iteration cap and incurs significant activity run fees.

Extension Wildcards (*.xlsx): Wildcarding by extension still matches files named ~$filename.xlsx, leading to the same file lock exception.

Source Server Scripts / Direct Databricks: On-premises security policies prohibit executing scripts on the source server, and Databricks does not have line-of-sight to the on-prem file share.

Questions for the Community

Is there an native ADF feature or SMB source setting that allows a single Copy Activity to ignore locked ~$* files on an on-prem FileServer share?

Is prefix-based iteration (a*–z*) the standard best practice for handling OS file locks over SHIR, or is there a cleaner pattern to filter these out at the source?

Thanks in advance for your insights!

0 comments

1 answer

Your answer

Answer 1

Hi @Misbah Bin Hossain

I don't recommend bypassing the lock on files beginning with ~$.

Office creates ~$... files as temporary owner/lock files while the corresponding Word/Excel/PowerPoint document is open. They are part of Office's locking mechanism and normally disappear when you close the document cleanly.

If this happens during automated file processing, the safer approach is to exclude Office temporary files from the workflow (e.g., ~$.docx, ~$.xlsx, and ~$*.pptx), then process only the actual document after Office releases it.

Also, avoid repeatedly retrying or force-opening a locked temporary file. Another process accessing an Office temporary file at the wrong point can interfere with the Office save/rename operation, for example, antivirus software locking an Excel temporary file before Excel can rename it.

If your automation needs to react immediately when files appear, use a retry/backoff or file-stability check instead: ignore ~$ files and wait until the real Office document can be opened normally and its size/last-write time has stabilized before processing it.

If the ~$ file remains after you close all Office applications, first verify that no WINWORD.EXE, EXCEL.EXE, or other Office process still has the document open. The stale owner files can remain after an abnormal Office termination; once you're sure the document is no longer in use, you can remove the stale owner file.

So don't try to change Windows permissions or bypass the file handle. The better pattern is:

ignore ~$ → wait for the actual document to be released → process the actual document.

If you can share what is monitoring/processing the folder (PowerShell, FileSystemWatcher, Windows service, Python, backup software, etc.), we can suggest the cleanest way to implement that logic.

References:

Microsoft - “The document is locked for editing by another user”

Microsoft - Troubleshoot errors when saving Excel workbooks

Help make this community better for everyone: If this answer helped or resolved your issue, please accept it or upvote it. If not, share more details in a comment so we can continue the discussion and find the right solution. Thank you.