How to check if file is locked (SSIS C# Script Task)<\/h2>\n
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<\/a> with Get file lock status action<\/strong> or Use SSIS Validation Task<\/a> which has option to throw error on lock condition or you can continue by saving lock status into variable and continue without throwing error. <\/p>\n Now lets check real example which will first check for locked file using SSIS Validation Task<\/a> and then if file is locked then wait until file is unlocked (with some timeout hardcoded in script). <\/p>\n If specified wait time is reached then script will throw error. Once file is unlocked SSIS Advanced File System Task<\/a> will copy file to target.<\/p>\n SSIS – How to check if file is locked. Wait until file is unlocked (C# Code – SSIS Script Task).<\/p><\/div>\n SSIS Validation Task -Check if file is locked. Save status to variable or throw error.<\/p><\/div>\n SSIS Advanced File System Task. Copy, Move, Rename, Delete multiple files<\/p><\/div>\n Below sample will work only if you have SSIS PowerPack<\/a> Installed. Download it from here<\/a> it will take only 1 minute to install\/\/ Attempt to open the file exclusively. -- If you get erro means file is locked\r\nusing (FileStream fs = new FileStream(fullPath,\r\n\tFileMode.Open, FileAccess.ReadWrite,\r\n\tFileShare.None, 100))\r\n{\r\n\tfs.ReadByte();\r\n\r\n\t\/\/ If we got this far the file is ready\r\n\tbreak;\r\n}<\/pre>\nHow to wait until file is unlocked (SSIS C# Script Task)<\/h2>\n
![\"SSIS](\"\/\/zappysys.com\/blog\/wp-content\/uploads\/2016\/02\/ssis-check-file-locked-wait-until-unlocked.png\")
<\/a>SSIS Validation Task -Store file lock status into variable<\/h3>\n
![\"SSIS](\"\/\/zappysys.com\/blog\/wp-content\/uploads\/2016\/02\/ssis-validation-task-check-if-file-is-locked.png\")
<\/a>SSIS Advanced File System Task – Copy, Move, Rename, Delete multiple files<\/h3>\n
![\"SSIS](\"\/\/zappysys.com\/blog\/wp-content\/uploads\/2016\/02\/ssis-advanced-file-system-task-how-to-copy-move-files.png\")
<\/a>SSIS C# Script Task – Check file is locked, wait until file is unlocked<\/h3>\n
#region Namespaces\r\nusing System;\r\nusing System.Data;\r\nusing Microsoft.SqlServer.Dts.Runtime;\r\nusing System.Windows.Forms;\r\nusing System.IO;\r\n#endregion\r\n\r\nnamespace ST_334a75922c6a47a5b4ac21ee8e4e5fff\r\n{\r\n\t[Microsoft.SqlServer.Dts.Tasks.ScriptTask.SSISScriptTaskEntryPointAttribute]\r\n\tpublic partial class ScriptMain : Microsoft.SqlServer.Dts.Tasks.ScriptTask.VSTARTScriptObjectModelBase\r\n\t{\r\n\t\tpublic void Main()\r\n\t\t{\r\n\t\t\t\/\/ TODO: Add your code here\r\n try\r\n {\r\n var file = Dts.Variables[\"User::filePath\"].Value.ToString();\r\n\r\n bool continueIfFileMissing = false;\r\n if (!System.IO.File.Exists(file))\r\n {\r\n if (continueIfFileMissing)\r\n {\r\n Dts.TaskResult = (int)ScriptResults.Success;\r\n return;\r\n }\r\n LogError(\"File not found: \" + file);\r\n Dts.TaskResult = (int)ScriptResults.Failure;\r\n }\r\n \r\n WaitForFile(file, maxWaitInSec:30);\r\n Dts.TaskResult = (int)ScriptResults.Success;\r\n }\r\n catch (Exception ex)\r\n {\r\n LogError(ex.Message);\r\n Dts.TaskResult = (int)ScriptResults.Failure;\r\n } \r\n\t\t\t\r\n\t\t}\r\n \/\/\/ <summary>\r\n \/\/\/ Blocks until the file is not locked any more.\r\n \/\/\/ <\/summary>\r\n \/\/\/ <param name=\"fullPath\"><\/param>\r\n bool WaitForFile(string fullPath,int maxWaitInSec)\r\n {\r\n int numTries = 0;\r\n while (true)\r\n {\r\n ++numTries;\r\n try\r\n {\r\n \/\/ Attempt to open the file exclusively.\r\n using (FileStream fs = new FileStream(fullPath,\r\n FileMode.Open, FileAccess.ReadWrite,\r\n FileShare.None, 100))\r\n {\r\n fs.ReadByte();\r\n\r\n \/\/ If we got this far the file is ready\r\n break;\r\n }\r\n }\r\n catch (Exception ex)\r\n {\r\n if(numTries==1 || numTries % 20 ==0 )\r\n LogWarning(string.Format(\"WaitForFile {0} failed to get an exclusive lock: {1}\",fullPath, ex.Message));\r\n\r\n \/\/if (numTries > 10)\r\n \/\/{\r\n \/\/ LogWarning(string.Format(\r\n \/\/ \"WaitForFile {0} giving up after 10 tries\",\r\n \/\/ fullPath));\r\n \/\/ return false;\r\n \/\/}\r\n\r\n if (numTries >= maxWaitInSec * 2)\r\n {\r\n throw new Exception(\"Max wait time reached for file : \" + fullPath + \". Waited for \" + maxWaitInSec + \" seconds but lock not released\");\r\n }\r\n\r\n \/\/ Wait for the lock to be released\r\n System.Threading.Thread.Sleep(500);\r\n }\r\n\r\n \r\n }\r\n\r\n LogInformation( string.Format( \"WaitForFile {0} returning true after {1} tries\",fullPath, numTries));\r\n return true;\r\n }\r\n\r\n private void LogInformation(string msg)\r\n {\r\n bool again = false;\r\n Dts.Events.FireInformation(0, \"ScriptTask\", msg, \"\", 0,ref again);\r\n }\r\n \r\n private void LogError(string msg)\r\n {\r\n Dts.Events.FireError(0, \"ScriptTask\", msg, \"\", 0);\r\n }\r\n private void LogWarning(string msg)\r\n {\r\n Dts.Events.FireWarning(0, \"ScriptTask\", msg, \"\", 0);\r\n }\r\n #region ScriptResults declaration\r\n \/\/\/ <summary>\r\n \/\/\/ This enum provides a convenient shorthand within the scope of this class for setting the\r\n \/\/\/ result of the script.\r\n \/\/\/ \r\n \/\/\/ This code was generated automatically.\r\n \/\/\/ <\/summary>\r\n enum ScriptResults\r\n {\r\n Success = Microsoft.SqlServer.Dts.Runtime.DTSExecResult.Success,\r\n Failure = Microsoft.SqlServer.Dts.Runtime.DTSExecResult.Failure\r\n };\r\n #endregion\r\n\r\n\t}\r\n}<\/pre>\nDownload Sample Package<\/h2>\n
\nDownload SSIS 2012 – Sample Package (Process Locked file)<\/a><\/p>\nConclusion<\/h2>\n