Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

File System Task > Move Files > Error - Process cannot access the file because its being used by another process Expand / Collapse
Author
Message
Posted Tuesday, February 9, 2010 12:01 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Monday, March 1, 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
Posted Tuesday, February 9, 2010 12:12 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 12:51 AM
Points: 5,245, Visits: 12,164
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.

When you ask a question (and please do ask a question: "My T-SQL does not work" just doesn't cut it), please provide enough information for us to understand its context.
Post #862656
Posted Tuesday, February 9, 2010 12:24 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

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

I've already tried this and receive the same error

Chris
Post #862666
Posted Tuesday, February 9, 2010 1:31 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Monday, March 1, 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
Posted Tuesday, February 9, 2010 3:36 PM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Wednesday, October 23, 2013 5:50 PM
Points: 257, Visits: 601
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
Posted Wednesday, April 4, 2012 5:36 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum 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
Posted Tuesday, February 5, 2013 2:38 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Monday, October 20, 2014 10:06 AM
Points: 5, Visits: 63
Thank you sooo much. After many days - this is the only solution that worked.
Post #1416111
Posted Wednesday, April 24, 2013 12:04 PM


SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: 2 days ago @ 1:04 PM
Points: 420, Visits: 996
Rather than maintain two copies of the same file, Import then move the file all in one loop.
Post #1446158
Posted Tuesday, January 28, 2014 11:05 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Friday, July 25, 2014 3:31 AM
Points: 27, Visits: 55
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!
Post #1535713
Posted Tuesday, January 28, 2014 11:06 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Friday, July 25, 2014 3:31 AM
Points: 27, Visits: 55
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!
Post #1535714
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse