How to Check if a File is Locked Before Reading a File in SSIS

SSIS PowerPack CSV
, ,
1

Sometimes, while reading a file in SSIS, you may encounter the following error:

The process cannot access the file because it is being used by another process.

Here we’re considering an example for a CSV file, but you can check any type of file’s locked status.

This usually happens when another application (Excel, Notepad, external process, FTP sync tool, etc.) has locked the file while your SSIS package is trying to read it.

In such cases, you can implement a simple retry/wait mechanism using ZS Advanced File System Task before executing the CSV Source.

Solution Overview

The recommended approach is:

  1. Use a For Loop Container
  2. Check whether the file is locked using ZS Advanced File System Task
  3. Save the lock status into a variable using GetFileLockFlag
  4. If the file is locked, wait and retry
  5. Once unlocked, continue with the Data Flow Task

Step-by-Step Implementation

Step 1 - Create Variables

Create the following SSIS variables:

Variable Name Data Type Default Value
User::IsFileLocked Boolean True
User::RetryCount Int32 0

Step 2 - Add For Loop Container

Configure the For Loop Container as follows:

InitExpression

@RetryCount = 0

EvalExpression

@IsFileLocked == TRUE && @RetryCount < 10

AssignExpression

@RetryCount = @RetryCount + 1

This loop retries file access until the file becomes available or maximum retries are reached.

Step 3 - Add ZS Advanced File System Task

Inside the loop, add ZS Advanced File System Task.

Configure:

  • Action: Check File Locked
  • File Path: Your CSV file path
  • Enable: GetFileLockFlag
  • Variable: User::IsFileLocked

This task checks whether another process currently holds a lock on the file.

Documentation:

ZS Advanced File System Task Documentation

Step 4 - Add Delay Between Retries

If the file is locked, add a small delay before retrying.

You can use the ZS Timer Task and configure it to wait for 5 seconds.

This waits for 5 seconds before retrying.

Step 5 - Execute Data Flow Task

Once the file becomes available (IsFileLocked = False), proceed with your Data Flow Task and read the CSV file using ZappySys CSV Source.

High-Level Workflow

For Loop Container
   |
   +-- ZS Advanced File System Task
   |      -> Check File Locked
   |      -> Save result into variable
   |
   +-- Delay / Wait
   |
   +-- Retry Until File Available
   |
   +-- Data Flow Task
           -> Read CSV File

Benefits of This Approach

  • Prevents intermittent package failures
  • Handles temporary file locks gracefully
  • Useful for shared folders and automated file drops
  • Easy to implement in existing SSIS packages

Related Links