Introduction
In this small blog post you will learn How to move files using SSIS Advanced File System Task and How to wait until file is unlocked using C# Script Task.
How to check if file is locked (SSIS C# Script Task)
If you want to check if file is locked in C# then below code will do the trick. But no worry if you dont know C#. ZappySys SSIS PowerPack comes with two tasks which can solve this issue without coding. You can use SSIS Advanced File System Task with Get file lock status action or Use SSIS Validation Task which has option to throw error on lock condition or you can continue by saving lock status into variable and continue without throwing error.
|
1 2 3 4 5 6 7 8 9 10 |
// Attempt to open the file exclusively. -- If you get erro means file is locked using (FileStream fs = new FileStream(fullPath, FileMode.Open, FileAccess.ReadWrite, FileShare.None, 100)) { fs.ReadByte(); // If we got this far the file is ready break; } |
How to wait until file is unlocked (SSIS C# Script Task)
Now lets check real example which will first check for locked file using SSIS Validation Task and then if file is locked then wait until file is unlocked (with some timeout hardcoded in script).
If specified wait time is reached then script will throw error. Once file is unlocked SSIS Advanced File System Task will copy file to target.
SSIS – How to check if file is locked. Wait until file is unlocked (C# Code – SSIS Script Task).
SSIS Validation Task -Store file lock status into variable
SSIS Validation Task -Check if file is locked. Save status to variable or throw error.
SSIS Advanced File System Task – Copy, Move, Rename, Delete multiple files
SSIS Advanced File System Task. Copy, Move, Rename, Delete multiple files
SSIS C# Script Task – Check file is locked, wait until file is unlocked
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 |
#region Namespaces using System; using System.Data; using Microsoft.SqlServer.Dts.Runtime; using System.Windows.Forms; using System.IO; #endregion namespace ST_334a75922c6a47a5b4ac21ee8e4e5fff { [Microsoft.SqlServer.Dts.Tasks.ScriptTask.SSISScriptTaskEntryPointAttribute] public partial class ScriptMain : Microsoft.SqlServer.Dts.Tasks.ScriptTask.VSTARTScriptObjectModelBase { public void Main() { // TODO: Add your code here try { var file = Dts.Variables["User::filePath"].Value.ToString(); bool continueIfFileMissing = false; if (!System.IO.File.Exists(file)) { if (continueIfFileMissing) { Dts.TaskResult = (int)ScriptResults.Success; return; } LogError("File not found: " + file); Dts.TaskResult = (int)ScriptResults.Failure; } WaitForFile(file, maxWaitInSec:30); Dts.TaskResult = (int)ScriptResults.Success; } catch (Exception ex) { LogError(ex.Message); Dts.TaskResult = (int)ScriptResults.Failure; } } /// <summary> /// Blocks until the file is not locked any more. /// </summary> /// <param name="fullPath"></param> bool WaitForFile(string fullPath,int maxWaitInSec) { int numTries = 0; while (true) { ++numTries; try { // Attempt to open the file exclusively. using (FileStream fs = new FileStream(fullPath, FileMode.Open, FileAccess.ReadWrite, FileShare.None, 100)) { fs.ReadByte(); // If we got this far the file is ready break; } } catch (Exception ex) { if(numTries==1 || numTries % 20 ==0 ) LogWarning(string.Format("WaitForFile {0} failed to get an exclusive lock: {1}",fullPath, ex.Message)); //if (numTries > 10) //{ // LogWarning(string.Format( // "WaitForFile {0} giving up after 10 tries", // fullPath)); // return false; //} if (numTries >= maxWaitInSec * 2) { throw new Exception("Max wait time reached for file : " + fullPath + ". Waited for " + maxWaitInSec + " seconds but lock not released"); } // Wait for the lock to be released System.Threading.Thread.Sleep(500); } } LogInformation( string.Format( "WaitForFile {0} returning true after {1} tries",fullPath, numTries)); return true; } private void LogInformation(string msg) { bool again = false; Dts.Events.FireInformation(0, "ScriptTask", msg, "", 0,ref again); } private void LogError(string msg) { Dts.Events.FireError(0, "ScriptTask", msg, "", 0); } private void LogWarning(string msg) { Dts.Events.FireWarning(0, "ScriptTask", msg, "", 0); } #region ScriptResults declaration /// <summary> /// This enum provides a convenient shorthand within the scope of this class for setting the /// result of the script. /// /// This code was generated automatically. /// </summary> enum ScriptResults { Success = Microsoft.SqlServer.Dts.Runtime.DTSExecResult.Success, Failure = Microsoft.SqlServer.Dts.Runtime.DTSExecResult.Failure }; #endregion } } |
Download Sample Package
Below sample will work only if you have SSIS PowerPack Installed. Download it from here it will take only 1 minute to install
Download SSIS 2012 – Sample Package (Process Locked file)
Conclusion
Processing and detecting locked files in SSIS can be tricky but using small C# script it can save you some headache. Download Advanced File System Task to try many options not available in native File System Task.
Other Keywords:
Check locked files in SSIS
How to check whether file is locked or not in SSIS
Detect locked file in SSIS
Wait until file is unlocked using C# script
How to handle file locking issue in SSIS using C# script
