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.