SSIS: The process cannot access the file because it is being used by another process.

Standard

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:

ImportFromSpreadsheet

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.

RetainConnection

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

 

Advertisements

8 thoughts on “SSIS: The process cannot access the file because it is being used by another process.

      • Miguel

        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

        Like

  1. 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..

    Like

    • 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?

      Like

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s