asked
SSIS - Locked backup file.
SSIS Package (2008). Outside of the package: Backup created at remote site, compressed and encrypted (7zip) and sent to me via FTP. My package: is scheduled (SQL agent job) to copy the file to sql and restore it. Problem: If the ftp transfer from the remote site is not ready when my package runs, my package fails cause the file is locked by another process. Should I go for retry logic, where the package picks up the error and waits for 15 minutes and tries again? Other ideas on how to get around this locked file problem? (And I can not initiate the process from my side)
You could try the WMI watcher task to wait for the file to be written into the folder and then use the control flow from that to initiate the import. Might not be feasible though as I think it requires fairly high level permissions on the machine that hosts the folder you want to watch
I've only ever used it with relatively small files and so could pick up the creation - which will be no use for you. I would think it is possible as you can use WMI to get an awful lot of info on the state of the system. Beyond that, I can point you towards some info on WQL classes here:
http://msdn.microsoft.com/en-us/library/aa394554(v=vs.85) Hope that helps a bit at least
This custom made SSIS-task did the trick for me:
http://www.sqlis.com/sqlis/post/File-Watcher-Task.aspx It triggers when file transfer is finished, not when it is created.
16 People are following this question.
