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 ClassNote: 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.
Hi Joost,
ReplyDeleteThanks for a great article! Joost, I've got a bit of an issue. Well, two actually.
1) I'm not a programmer at all and thus I don't know how to change the code to look at an XLS file instead of an XLSX file. Currently it just refuses to look at a .XLS file. What do I need to change for it to look at the correct XLS type?
2) I need the code to post a value to a variable if the file is locked and then to exit. I've tried various methods of doing this but it doesn't work. If I use the code that you provided then it just hangs until I close my XLSX file. Any ideas?
Kind regards,
Colin
In line 30
Deletestring connectionString = Dts.Connections["myCsvFile"].ConnectionString; you can change the name to that of your Excel Connection Manager.
In line 39 and 40 you see how to get the path from the excel connection string.
If you don't want a recurring check you need to remove the while loop. And at this post you can see how you can fill a SSIS variable.
And if you don't want to program .net code you can use a custom task (see second and third point a solution list).
Hi Joost,
DeleteThanks for the speedy reply! I'd love to just use the .net task, however our IT policies state that 3rd party tools aren't allowed... Anyways. I managed to get the code to open up the .XLS file, however I am unsure what you mean by "remove the while loop"? If I understand it correctly then the while loop is supposed to be there. It'll check if the file is locked, and if not it will exit the code. If it is locked then currently it loops until the lock is released. If there is another issue with the file then it will do that in the second catch block. All I would like it to do is to instead of looping just pass a value to a parameter and then to exit.
I checked out the post you mentioned above and it'll work like a charm, I am just unsure of what to remove from the first catch block in order for it to not wait until the file lock is released.
Thanks for your help. Your website has been bookmarked. Very helpful stuff.
Kind regards,
Colin
You have to remove the while loop otherwise it waits until the file is unlocked. Alternative is to built in a max try mechanism.
DeleteRemove the following lines:
51-54 // start loop
82 // pause
89 // break loop
91 // end loop
On the bottom of the try block you can set the new SSIS variable to true.
And in the first catch block you can set the new SSIS variable to false.
Thanks this code worked in with C# 2008 SSIS. Can you please provide me the same code in VB script as am using 2005 SSIS. I tried to convert but its gets a infinite loop.
ReplyDeleteThanks in advance!!!
I added a VB.Net version, but it's created in 2012. So you have to replace my Script Result line (success and failure) with the original from 2005 (see your main method).
DeletePS I use this to convert C# to VB.Net
How to exit the sleep thread after 5 attempts
ReplyDeleteCreate an integer counter variable right above the loop. Increase that number within the loop.
DeleteThen add an if-construction within the try-catch where you break the loop and fail the Script Task if the counter is 5 or higher. Let me know if you have any issues.
I have already added such feature within my File-in-use Task.