File System Task > Move Files > Error - Process cannot access the file because its being used by another process

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » SQL Server 2005 » SQL Server 2005 Integration Services » File System Task > Move Files > Error -...

File System Task > Move Files > Error - Process cannot access the file because its being used by another process

Rate Topic

Display Mode

Topic Options

Author

Message

chrismgarvey

Posted Tuesday, February 09, 2010 12:01 PM

Grasshopper

Group: General Forum Members
Last Login: Monday, March 01, 2010 1:26 AM
Points: 24, Visits: 160

Hi,

I have 2 foreach loop containers within my control flow:

The first foreach loop container loops through a directory and loads files into a table

The second foreach loop container loops through the same directory and moves the files to an archive directory.

When executing the package I receive the following error message:

[File System Task] Error: An error occurred with the following error message: "The process cannot access the file because it is being used by another process".

Any suggestions would be greatly appreciated.

Regards,
Chris

Post #862651

Phil Parkin

Posted Tuesday, February 09, 2010 12:12 PM

SSCarpal Tunnel

Group: General Forum Members
Last Login: Today @ 9:39 AM
Points: 4,247, Visits: 9,500

Why not use a single Foreach loop to do the entire load/archive process?

____________________________________________________________________________________________

Help us to help you. For better, quicker and more focused answers to your questions, consider following the advice in this link:

http://www.sqlservercentral.com/articles/Best+Practices/61537/

If you are asking for help and your post does not contain a question, you should expect responses which do not contain any answers. Put a question mark in there somewhere - it's not rocket science.

Post #862656

chrismgarvey

Posted Tuesday, February 09, 2010 12:24 PM

Grasshopper

Group: General Forum Members
Last Login: Monday, March 01, 2010 1:26 AM
Points: 24, Visits: 160

Hi Phil,

I've already tried this and receive the same error

Chris

Post #862666

chrismgarvey

Posted Tuesday, February 09, 2010 1:31 PM

Grasshopper

Group: General Forum Members
Last Login: Monday, March 01, 2010 1:26 AM
Points: 24, Visits: 160

Full details of package as follows:

Loop 1 (looks for Excel file)
loop2 (looks for Excel sheet using ado.net schema rowset)
DFT
End loop 1
End loop 2
|
|
\/
Loop 3
Move file
End Loop3

Could the ado.net schema rowset, be causing issues?

Chris

Post #862709

Aparna-1

Posted Tuesday, February 09, 2010 3:36 PM

SSC Veteran

Group: General Forum Members
Last Login: Thursday, June 14, 2012 11:57 PM
Points: 257, Visits: 595

It might be possible that the file download on your source directory is not complete and at the same instance you are trying to move the file. 2 Processes are accessing the same resource.

Create an onfailure event and loop through the rest of the files in the folder or write a script task to Open each file (catch an exception if the file cannot be open) before moving.

Post #862784

Rocco.Sansotta

Posted Wednesday, April 04, 2012 5:36 PM

Forum Newbie

Group: General Forum Members
Last Login: Friday, June 22, 2012 10:51 AM
Points: 2, Visits: 61

Since this took me MANY HOURS to solve, I'm posting my solution for others. And it was sooo simple...

Place a copy of your Excel File in another folder and name it "template.xlsx". Have your Schema connection point to this copy. The actual Excel file will no longer be kept locked by the ForEach Worksheet Schema loop!

Post #1278475

amendieta

Posted Tuesday, February 05, 2013 2:38 PM

Forum Newbie

Group: General Forum Members
Last Login: Tuesday, March 19, 2013 4:15 PM
Points: 5, Visits: 45

Thank you sooo much. After many days - this is the only solution that worked. Smile

Post #1416111

mmartin1

Posted Wednesday, April 24, 2013 12:04 PM

SSC-Enthusiastic

Group: General Forum Members
Last Login: Yesterday @ 12:11 PM
Points: 193, Visits: 642

Rather than maintain two copies of the same file, Import then move the file all in one loop.

Post #1446158

« Prev Topic | Next Topic »

Permissions