Monday, 2 May 2011

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

8 comments:

  1. Hi Joost,

    Thanks 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

    ReplyDelete
    Replies
    1. In line 30
      string 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).

      Delete
    2. Hi Joost,

      Thanks 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

      Delete
    3. You have to remove the while loop otherwise it waits until the file is unlocked. Alternative is to built in a max try mechanism.

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

      Delete
  2. 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.

    Thanks in advance!!!

    ReplyDelete
    Replies
    1. 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).

      PS I use this to convert C# to VB.Net

      Delete
  3. How to exit the sleep thread after 5 attempts

    ReplyDelete
    Replies
    1. Create an integer counter variable right above the loop. Increase that number within the loop.
      Then 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.

      Delete

Please use the SSIS MSDN forum for general SSIS questions that are not about this post. I'm a regular reader of that forum and will gladly answer those questions over there.

All comments are moderated manually to prevent spam.

Related Posts Plugin for WordPress, Blogger...