Fix “File Cannot Be Opened” Error in Azure Synapse | File Not Found or In Use Issue Explained | Azure Synapse Analytics Tutorial

Fix “File Cannot Be Opened” Error in Azure Synapse | File Not Found or In Use Issue Explained

📘 Overview

While working with Serverless SQL Pools or Spark Pools in Azure Synapse Analytics, you may encounter the frustrating error:

Msg 105019, Level 16, State 1, Line 1  
External file cannot be opened because it does not exist or is used by another process.

This error can occur when accessing files using OPENROWSET, external tables, or Spark reads from abfss:// paths.

❌ Common Causes

• The file or folder does not exist at the specified path
• You are referencing a folder but did not specify WITH (FORMAT = 'CSV') properly
• File is still being written to and locked by another process
• Synapse doesn't have permission to access the container or path
• File is empty or contains incompatible format/schema

✅ How to Fix It

🔹 1. Double-Check File Path

SELECT *  
FROM OPENROWSET(
    BULK 'https://yourstorage.dfs.core.windows.net/container/data/file.csv',
    FORMAT = 'CSV',
    PARSER_VERSION = '2.0'
) AS rows;

✅ Use Azure Storage Explorer to verify that the file and folder exist.

🔹 2. Use Trailing Slash for Folder Reads

-- Correct: reading all files from folder
BULK 'https://.../datafolder/'

🔹 3. Check File Permissions

Ensure Synapse has Storage Blob Data Contributor role assigned via IAM or Managed Identity.

🔹 4. Validate File Format

Make sure your file is in the correct format and that headers, delimiters, or encodings are compatible with your query options.

🔹 5. Delay Access if File Is in Use

If the file is still being written by another service (e.g., ADF pipeline or streaming job), introduce a delay or check for file locks before reading.

📌 Best Practices

• Always verify storage path and access manually when error occurs
• Use wildcards to test file patterns if unsure
• Enable diagnostic logs for more details

📈 Helpful Diagnostic Query

-- To test path access
SELECT TOP 10 *  
FROM OPENROWSET(
    BULK 'https://.../testfolder/*.csv',
    FORMAT = 'CSV',
    FIRSTROW = 2
) AS rows;

📺 Watch the Video Tutorial

📚 Credit: Content created with the help of ChatGPT and Gemini.