Wait until file is unlocked

Case
I often get errors that my source file is still locked by an other process. Is there a solution to wait until the file is released?

Solution
You could make a private copy of the source file with the File System Task but you can also wait for the unlock. Here are a couple of solutions:

• You can built a simple wait event to wait a couple of seconds/minutes, but for how long?
• You can built a construction with a Loop Container and the open source File Property Task, which has a FileIsReadable property.
• You can use the third party File-in-use Task.
• You can do it your self with a Script Task:

1) Script Task

Add a Script Task to the Control Flow and connect it to your Data Flow Task.

Script Task

2) The script
Copy the following script to the Script Task. If you use 2005 then the script result is a little different. Replace the my script result line with the original line from your main method.

// C# code
using System;
using System.Data;
using Microsoft.SqlServer.Dts.Runtime;
using System.Windows.Forms;
using System.IO;                        // Added to check file existance
using System.Threading;                 // Added for delay

namespace ST_89ab5f10e1de490aa762819b8221ee0a.csproj
{
    [System.AddIn.AddIn("ScriptMain", Version = "1.0", Publisher = "", Description = "")]
    public partial class ScriptMain : Microsoft.SqlServer.Dts.Tasks.ScriptTask.VSTARTScriptObjectModelBase
    {

        #region VSTA generated code
        enum ScriptResults
        {
            Success = Microsoft.SqlServer.Dts.Runtime.DTSExecResult.Success,
            Failure = Microsoft.SqlServer.Dts.Runtime.DTSExecResult.Failure
        };
        #endregion

        public void Main()
        {
            // Variable to store the file path in.
            string myConnectionString = "";

            // For flat files like csv and txt you can use the whole connectionstring
            // ======================================================================
            string connectionString = Dts.Connections["myCsvFile"].ConnectionString;


            // For Excel connection you only need a part of the connectionstring:
            // ======================================================================
            // Provider=Microsoft.Jet.OLEDB.4.0;Data Source=D:\MyExcelFile.xls;Extended Properties="Excel 8.0;HDR=YES";
            // Provider=Microsoft.ACE.OLEDB.12.0;Data Source=D:\MyExcelFile.xlsx;Extended Properties="Excel 12.0 XML;HDR=YES";
            // ======================================================================
            // You only want the part after 'Source=' until the next semicolon (;)
            // myConnectionString = Dts.Connections["myExcelFile"].ConnectionString.Substring(Dts.Connections["myExcelFile"].ConnectionString.IndexOf("Source=") + 6);
            // myConnectionString = myConnectionString.Substring(1, myConnectionString.IndexOf(";") - 1);

            // Check if the file exists before checking if it can be opened
            if (File.Exists(myConnectionString))
            {
                Boolean fireAgain = false;
                Dts.Events.FireInformation(0, "File Lock Check", "File exists, now checking if it can be opened", string.Empty, 0, ref fireAgain);

                // Boolean variable to prevent endless lock warnings
                Boolean ShowLockWarning = true;

                // Boolean variable needed for the while loop
                Boolean FileLocked = true;
                while (FileLocked)
                {
                    try
                    {
                        // Check if the file isn't locked by an other process by opening 
                        // the file. If it succeeds, set variable to false and close stream                     
                        FileStream fs = new FileStream(myConnectionString, FileMode.Open);

                        // No error so it is not locked
                        Dts.Events.FireInformation(0, "File Lock Check", "File not locked", string.Empty, 0, ref fireAgain);
                        FileLocked = false;

                        // Close the file and exit the Script Task
                        fs.Close();
                        Dts.TaskResult = (int)ScriptResults.Success;
                    }
                    catch (IOException ex)
                    {
                        // If opening fails, it's probably locked by an other process. This is the exact message:
                        // System.IO.IOException: The process cannot access the file 'D:\example.csv' because it is being used by another process. 

                        // Log locked status (once)
                        if (ShowLockWarning)
                        {
                            Dts.Events.FireWarning(0, "File Lock Check", "File locked: " + ex.Message, string.Empty, 0);
                        }
                        ShowLockWarning = false;

                        // Wait two seconds before rechecking
                        Thread.Sleep(2000);
                    }
                    catch (Exception ex)
                    {
                        // Catch other unexpected errors and break the while loop
                        Dts.Events.FireError(0, "File Lock Check", "Unexpected error: " + ex.Message, string.Empty, 0);
                        Dts.TaskResult = (int)ScriptResults.Failure;
                        break;
                    }
                }
            }
            else
            {
                // File doesn't exist, so no checking possible.
                Dts.Events.FireError(0, "File Lock Check", "File does not exist: " + myConnectionString, string.Empty, 0);
                Dts.TaskResult = (int)ScriptResults.Failure;
            }
        }
    }
}

or VB.Net code

'VB.Net code
Imports System
Imports System.Data
Imports System.Math
Imports Microsoft.SqlServer.Dts.Runtime
Imports System.IO           ' Added to check file existance
Imports System.Threading    ' Added for delay

<Microsoft.SqlServer.Dts.Tasks.ScriptTask.SSISScriptTaskEntryPointAttribute()> _
<System.CLSCompliantAttribute(False)> _
Partial Public Class ScriptMain
    Inherits Microsoft.SqlServer.Dts.Tasks.ScriptTask.VSTARTScriptObjectModelBase

    Public Sub Main()
        ' Variable to store the file path in.
        Dim myConnectionString As String = ""

        ' For flat files like csv and txt you can use the whole connectionstring
        ' ======================================================================
        myConnectionString = Dts.Connections("myCsvFile").ConnectionString

        ' For Excel connection you only need a part of the connectionstring:
        ' ======================================================================
        ' Provider=Microsoft.Jet.OLEDB.4.0;Data Source=D:\MyExcelFile.xls;Extended Properties="Excel 8.0;HDR=YES";
        ' Provider=Microsoft.ACE.OLEDB.12.0;Data Source=D:\MyExcelFile.xlsx;Extended Properties="Excel 12.0 XML;HDR=YES";
        ' ======================================================================
        ' You only want the part after 'Source=' until the next semicolon (;)
        ' myConnectionString = Dts.Connections("myExcelFile").ConnectionString.Substring(Dts.Connections("myExcelFile").ConnectionString.IndexOf("Source=") + 6)
        ' myConnectionString = myConnectionString.Substring(1, myConnectionString.IndexOf(";") - 1)

        ' Check if the file exists before checking if it can be opened
        If File.Exists(myConnectionString) Then
            Dim fireAgain As [Boolean] = False
            Dts.Events.FireInformation(0, "File Lock Check", "File exists, now checking if it can be opened", String.Empty, 0, fireAgain)

            ' Boolean variable to prevent endless lock warnings
            Dim ShowLockWarning As [Boolean] = True

            ' Boolean variable needed for the while loop
            Dim FileLocked As [Boolean] = True
            While FileLocked
                Try
                    ' Check if the file isn't locked by an other process by opening 
                    ' the file. If it succeeds, set variable to false and close stream                     
                    Dim fs As New FileStream(myConnectionString, FileMode.Open)

                    ' No error so it is not locked
                    Dts.Events.FireInformation(0, "File Lock Check", "File not locked", String.Empty, 0, fireAgain)
                    FileLocked = False

                    ' Close the file and exit the Script Task
                    fs.Close()
                    Dts.TaskResult = ScriptResults.Success
                Catch ex As IOException
                    ' If opening fails, it's probably locked by an other process. This is the exact message:
                    ' System.IO.IOException: The process cannot access the file 'D:\example.csv' because it is being used by another process. 

                    ' Log locked status (once)
                    If ShowLockWarning Then
                        Dts.Events.FireWarning(0, "File Lock Check", "File locked: " & ex.Message, String.Empty, 0)
                    End If
                    ShowLockWarning = False

                    ' Wait two seconds before rechecking
                    Thread.Sleep(2000)
                Catch ex As Exception
                    ' Catch other unexpected errors and break the while loop
                    Dts.Events.FireError(0, "File Lock Check", "Unexpected error: " & ex.Message, String.Empty, 0)
                    Dts.TaskResult = ScriptResults.Failure
                    Exit Try
                End Try
            End While
        Else
            ' File doesn't exist, so no checking possible.
            Dts.Events.FireError(0, "File Lock Check", "File does not exist: " & myConnectionString, String.Empty, 0)
            Dts.TaskResult = ScriptResults.Failure
        End If
    End Sub

    Enum ScriptResults
        Success = Microsoft.SqlServer.Dts.Runtime.DTSExecResult.Success
        Failure = Microsoft.SqlServer.Dts.Runtime.DTSExecResult.Failure
    End Enum

End Class

Note: you can check both Excel files and flat files like Csv and fixed. Comment out the Flat file or Excel part of the Script.

3) The result
Now you can test your package by opening the source file in for example Excel and see the result:

The test result

Note: you could add some counter or time compare mechanism to accomplish a max number of checks or a max check time.This will prevent endless waiting.