question

raadee avatar image
raadee 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)
ssis
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

tomgough79 avatar image
tomgough79 answered
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
5 comments
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

another option I've seen used is to write an empty "OK" file when the FTP transfer is complete. Begin the copy/unzip/restore when the OK file shows up.
2 Likes 2 ·
Sounds interesting. Permissions is not an issue on my side since i host the ftp. The file backup file is written to the folder but it takes 2 hours for it to be complete (ftp transfer). Can this task differentiate between a locked file and a non-locked file?
0 Likes 0 ·
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
0 Likes 0 ·
It did help, I will check it out. Thanks Tom
0 Likes 0 ·
Thanks Ken, will have a look at that to.
0 Likes 0 ·
raadee avatar image
raadee answered
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.
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.