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

Question

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

chrismgarvey

SSC Eights!

Points: 834
More actions
February 9, 2010 at 12:01 pm

#142162

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

Viewing 10 posts - 1 through 9 (of 9 total)

You must be logged in to reply to this topic. Login to reply

Phil Parkin SSC Guru Points: 246959 More actions · Answer 1

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

The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin

chrismgarvey SSC Eights! Points: 834 More actions · Answer 2

Hi Phil,

I've already tried this and receive the same error

Chris

chrismgarvey SSC Eights! Points: 834 More actions · Answer 3

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

Aparna-1 SSCrazy Points: 2339 More actions · Answer 4

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.

Rocco.Sansotta SSC Enthusiast Points: 158 More actions · Answer 5

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!

amendieta Old Hand Points: 307 More actions · Answer 6

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

MMartin1 One Orange Chip Points: 27795 More actions · Answer 7

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

----------------------------------------------------

superi SSC Enthusiast Points: 191 More actions · Answer 8

Well, i saw the same issue & there is a property on Excel connection manger "RetainSameConnection". If you set this to "False" , Viola, the error disappears!

superi SSC Enthusiast Points: 191 More actions · Answer 9

Well, i saw the same issue & there is a property on Excel connection manger "RetainSameConnection". If you set this to "False" , Viola, the error disappears!