I have a SSIS package that loads data from excel to a SQL server table. There is a script task which checks File.Exists(fle) and sets a variable value. The package will send mail if the File does not exist or it will load SQL table if it exists. Now i want to add a check if file is opened by somebody else. How to do it in script task. I jus want to know if its open but I dont want to open the file,because excel connection source fails when the excel is open. Thank in advance.

Try opening it in a filestream. (You can even add a while loop around it) public void Main() { Boolean FileLocked = true; while (FileLocked) { // Check if the file isn't locked by an other process try { // Try to open the file. If it succeeds, set variable to false and close stream FileStream fs = new FileStream(Dts.Variables["User::FilePath"].Value.ToString(), FileMode.Open); FileLocked = false; fs.Close(); Dts.TaskResult = (int)ScriptResults.Success; } catch (UnauthorizedAccessException ex) { // If opening fails, it's probably locked by an other process FileLocked = true; // Wait two seconds before rechecking Thread.Sleep(2000); } catch (Exception ex) { // Catch other errors, like file doesn't exists Dts.Events.FireError(0, "Wait until file is released", ex.Message, string.Empty, 0); Dts.TaskResult = (int)ScriptResults.Failure; break; } } } An other option is the file property task: http://filepropertiestask.codeplex.com/ It has an in-use check. Please mark the post as answered if it answers your question | My SSIS Blog: http://microsoft-ssis.blogspot.com

Try opening it in a filestream. (You can even add a while loop around it) public void Main() { Boolean FileLocked = true; while (FileLocked) { // Check if the file isn't locked by an other process try { // Try to open the file. If it succeeds, set variable to false and close stream FileStream fs = new FileStream(Dts.Variables["User::FilePath"].Value.ToString(), FileMode.Open); FileLocked = false; fs.Close(); Dts.TaskResult = (int)ScriptResults.Success; } catch (UnauthorizedAccessException ex) { // If opening fails, it's probably locked by an other process FileLocked = true; // Wait two seconds before rechecking Thread.Sleep(2000); } catch (Exception ex) { // Catch other errors, like file doesn't exists Dts.Events.FireError(0, "Wait until file is released", ex.Message, string.Empty, 0); Dts.TaskResult = (int)ScriptResults.Failure; break; } } } An other option is the file property task: http://filepropertiestask.codeplex.com/ It has an in-use check. Update: http://microsoft-ssis.blogspot.com/2011/05/wait-until-file-is-unlocked.html Please mark the post as answered if it answers your question | My SSIS Blog: http://microsoft-ssis.blogspot.com

Hi SSIS Joost, I tried the code given by you happily thinking that it would work. But in my case all the files are sent to the catch block. Is it due to some access issue?

Did you get a chance t o look into what is the error messge ? That you can get it from ex.Message in the catch block . Please post that for further analysis.

Hi SSIS Joost, I tried the code given by you happily thinking that it would work. But in my case all the files are sent to the catch block. Is it due to some access issue? if they are send to the first catch then the file exists but you can't access/open them. if they are send to the second catch there is an other error: they don't exist or something else (you can see that in the error message) Let me know to which catch the went and what the error was...Please mark the post as answered if it answers your question | My SSIS Blog: http://microsoft-ssis.blogspot.com

It went to the first catch block, which means 'I cant access them' Its because I have Read only access on that folder. Assuming I will never be granted with complete access, how can I handle this better? Or is it necessary to have complete access to the folder for this kind of operation? Added: One More Clarification Right now I am developing the package from my local machine. Hence, the package will work fine if My account name is granted with full access rights. But I have to deploy the package in the production server and schedule the job from production server. In That case, the 'Run As' proxy needs to have complete access right?

It went to the first catch block, which means 'I cant access them' Its because I have Read only access on that folder. Assuming I will never be granted with complete access, how can I handle this better? Or is it necessary to have complete access to the folder for this kind of operation? try to see if there is a differnce in the precise error if you open one of the files. Add something like messagebox.show(Dts.Variables["User::FilePath"].Value.ToString() + " : " + ex.Message); You could also check this FileMode info at msdn: http://msdn.microsoft.com/en-us/library/system.io.filemode.aspxPlease mark the post as answered if it answers your question | My SSIS Blog: http://microsoft-ssis.blogspot.com

The code you have given worked. Although the code does not work with my login id, it works fine with the proxy with which job is created. The proxy has all the rights to tht file. Thanks.

Here is a new code example: http://microsoft-ssis.blogspot.com/2011/05/wait-until-file-is-unlocked.html Please mark the post as answered if it answers your question | My SSIS Blog: http://microsoft-ssis.blogspot.com