tag:blogger.com,1999:blog-2303058199815958946.post8647635969751327206..comments2024-03-28T10:36:35.016+01:00Comments on Microsoft SQL Server Integration Services: Wait until file is unlockedJoost van Rossumhttp://www.blogger.com/profile/01125981589974671317noreply@blogger.comBlogger8125tag:blogger.com,1999:blog-2303058199815958946.post-29277058478084679122014-11-14T10:19:19.568+01:002014-11-14T10:19:19.568+01:00Create an integer counter variable right above the...Create an integer counter variable right above the loop. Increase that number within the loop.<br />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.<br /><br />I have already added such feature within my <a href="http://microsoft-ssis.blogspot.com/2012/01/custom-ssis-component-file-in-use-task.html" rel="nofollow">File-in-use Task</a>.Joost van Rossumhttps://www.blogger.com/profile/01125981589974671317noreply@blogger.comtag:blogger.com,1999:blog-2303058199815958946.post-16425397643133639682014-11-12T20:37:37.732+01:002014-11-12T20:37:37.732+01:00How to exit the sleep thread after 5 attemptsHow to exit the sleep thread after 5 attemptsjosehttps://www.blogger.com/profile/10836686276318658273noreply@blogger.comtag:blogger.com,1999:blog-2303058199815958946.post-19216039178172431312013-01-02T16:14:15.345+01:002013-01-02T16:14:15.345+01:00I added a VB.Net version, but it's created in ...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).<br /><br />PS I use <a href="http://www.developerfusion.com/tools/convert/csharp-to-vb/" rel="nofollow">this</a> to convert C# to VB.NetJoost van Rossumhttps://www.blogger.com/profile/01125981589974671317noreply@blogger.comtag:blogger.com,1999:blog-2303058199815958946.post-25810397181426930362013-01-02T15:44:20.243+01:002013-01-02T15:44:20.243+01:00Thanks this code worked in with C# 2008 SSIS. Can ...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.<br /><br />Thanks in advance!!!Deepuhttps://www.blogger.com/profile/12653243727071040259noreply@blogger.comtag:blogger.com,1999:blog-2303058199815958946.post-74700459374092535572012-09-17T16:15:50.159+02:002012-09-17T16:15:50.159+02:00You have to remove the while loop otherwise it wai...You have to remove the while loop otherwise it waits until the file is unlocked. Alternative is to built in a max try mechanism.<br /><br />Remove the following lines:<br />51-54 // start loop<br />82 // pause<br />89 // break loop<br />91 // end loop<br /><br />On the bottom of the try block you can set the new SSIS variable to true.<br />And in the first catch block you can set the new SSIS variable to false.Joost van Rossumhttps://www.blogger.com/profile/01125981589974671317noreply@blogger.comtag:blogger.com,1999:blog-2303058199815958946.post-75490952869105044242012-09-17T15:26:30.971+02:002012-09-17T15:26:30.971+02:00Hi Joost,
Thanks for the speedy reply! I'd lo...Hi Joost,<br /><br />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.<br /><br />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.<br /><br />Thanks for your help. Your website has been bookmarked. Very helpful stuff.<br /><br />Kind regards,<br />ColinColinhttps://www.blogger.com/profile/13327852973923890013noreply@blogger.comtag:blogger.com,1999:blog-2303058199815958946.post-22846537751132373062012-09-17T15:01:21.992+02:002012-09-17T15:01:21.992+02:00In line 30
string connectionString = Dts.Connectio...In line 30<br /><strong>string connectionString = Dts.Connections["myCsvFile"].ConnectionString;</strong> you can change the name to that of your Excel Connection Manager.<br />In line 39 and 40 you see how to get the path from the excel connection string.<br /><br /><br />If you don't want a recurring check you need to remove the while loop. And at this <a href="http://microsoft-ssis.blogspot.com/2010/12/how-to-use-variables-in-script-task.html" rel="nofollow">post</a> you can see how you can fill a SSIS variable.<br /><br /><br />And if you don't want to program .net code you can use a custom task (see second and third point a solution list).Joost van Rossumhttps://www.blogger.com/profile/01125981589974671317noreply@blogger.comtag:blogger.com,1999:blog-2303058199815958946.post-10597420535195868592012-09-17T14:39:42.298+02:002012-09-17T14:39:42.298+02:00Hi Joost,
Thanks for a great article! Joost, I...Hi Joost,<br /><br />Thanks for a great article! Joost, I've got a bit of an issue. Well, two actually. <br /><br />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?<br /><br />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?<br /><br />Kind regards,<br />ColinColinhttps://www.blogger.com/profile/13327852973923890013noreply@blogger.com