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:
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.
