AAAAAAAAAAAGGGGGGGGGGGHHHHHHHHHHHHHHHHHHHHHH!!!!!!
I’ve just spent a frustrating few minutes trying to figure out why my SSIS package fails to move an Excel file I’m using as a source to an archive location.
The package is pretty simple:
All it does is receive a folder location of x number of Excel files, loops over them and reads the data, then the Archive the imported files File Task moves them to an archive folder. I was encountering the infuriating error:
SSIS: The process cannot access the file because it is being used by another process.
The problem was that I had set the Retain Same Connection property to true for the SourceConnectionExcel connection manager.
This means that SSIS will lock this file until the package stops executing. In turn meaning you won’t be able to move the file in File Task operation. Once I’d altered the setting, my package worked without issue.
Feelsgoodman.jpg
Even set the Retain Same Connection property to false appears the same error
LikeLike
Is the file open anywhere else? If you open it manually do you get an error?
LikeLike
Thanks for your response, no, if I open manually process is working, problem is that I don’t know how much time file is blocked by previous process, in fact I added an SQL time delay process between load process and move files process
LikeLike
Yes so it sounds like the Retain Same Connection setting is being ignored? Is the load process completely finished by the time you’re trying to archive the files? It will only work as per the image in the post, where the archive runs after the load..
LikeLike
Yes the load process is completely finished, even constraint value = Completion
LikeLike
Yes but sounds like the connection won’t be dropped until the package finishes running. Shoot me an email at jim.m257@gmail.com if you like and we can have a look..
LikeLike
I am having the same exact issue as Miguel. Was a solution ever found for this?
LikeLike
No. Unfortunately! I’d have to try and replicate the issue myself as mine was solved by the post. Don’t suppose your SSIS package is generic enough to share?
LikeLike