For Each Loop - error accessing text file

Question

Post reply

For Each Loop - error accessing text file

dave-dj

SSChampion

Points: 11297
More actions
February 2, 2010 at 4:48 am

#137001

HELP?!
I've created a package to loop through the files in a directory and:
1. create a renamed copy of the file,
2. ftp the copy to a server
3. remove the copy file
4. Move the original file to a processed / not processed directory.
I've run this locally on my machine to develop and test and it runs fine. When I try to run this on the production server I get the error
The process cannot access the file. "C:\Test\atest.txt" because it is being used by another process
Nothing else is using it though?
Whats going on ??
_____________________________________________________________________________MCITP: Business Intelligence Developer (2005)

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

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

Jack Corbett SSC Guru Points: 184394 More actions · Answer 1

What have you done to verify that the file is not in use by another process?

Can you provide the OS?

Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question

dave-dj SSChampion Points: 11297 More actions · Answer 2

well since my last post, I've done loads of testing. After initially thinking it had something to do with me developing the package on my 32-bit system and then trying to run on a 64-bit system I looked into that.

Cutting out a lot of stuff inbetween, I completed recreated the package on the 64-bit server. (thinking that this should resolve the issue. It didn't.

I then created a simple package, that uses a File System Task to copy a file. (note: I specified the file names - they were not variables). This worked fine.

so I added in my For Each Loop container and assigned the file name to a variable. This variable then get used by the File System Task to copy the source file to a fixed fixed file destination (to rule out and issues there).

I've set the destination file to be overwritten if it exists, but it doesn't if the file already exists

I don't understand why this should be as the same process works fine on my 32-bit server?

Do you have any clues? Thanks

_____________________________________________________________________________MCITP: Business Intelligence Developer (2005)

dave-dj SSChampion Points: 11297 More actions · Answer 3

sorry forgot to say:

server is:

Windows Server 2003 SP2 64-bit

SQL Server Standard Edition SP3 64-bit

_____________________________________________________________________________MCITP: Business Intelligence Developer (2005)

dave-dj SSChampion Points: 11297 More actions · Answer 4

Ok,

I've gone back to testing on my own laptop (Vista 32-bit).

Here something very very confusing. If the file already existed in my chosen directory (I copied files from the live server), it will process fine.

If I create a new text document, then it simply doesn't process. I get the aforementioned error.... "File is being used by another process".

To test this I even restarted my machine after creating the file, but I still get the same error for the new file?

I'm a local admin on my machine and I'm running the package interactively through BIDS, so I can't see that it is a file permission issue.

Any further clues from anyone would be greatly appreciated. This is really frustrating me now.

_____________________________________________________________________________MCITP: Business Intelligence Developer (2005)

dave-dj SSChampion Points: 11297 More actions · Answer 5

guys and girls.

I'm still struggling to find the answer to this. Can anyone else help

_____________________________________________________________________________MCITP: Business Intelligence Developer (2005)

wschampheleer SSCertifiable Points: 5504 More actions · Answer 6

Can you upload a sample package so we can reproduce the error?

Regards,

Willem
http://wschampheleer.wordpress.com[/url]

dave-dj SSChampion Points: 11297 More actions · Answer 7

Here the sample package and also the Test folder that I put on C:

(i've removed the ftp server details etc for obvious reasons and disabled the step).

Thanks for trying to help - it's very appreciated.

_____________________________________________________________________________MCITP: Business Intelligence Developer (2005)

wschampheleer SSCertifiable Points: 5504 More actions · Answer 8

Dave,

I have the impression that in your first step (FST Rename Source File) you are trying to copy the file onto itself which raises the error.

This seems to happen because the DestionationFileName equals the SourceFolder (C:\Test). The DestinationFileName gets it value at runtime because the EvaluateAsExpression property is True. The expression used is @[User::SourceFolder] + "\\" + @[User::JDEMember] but @[User::JDEMember] is empty so it gets the value @[User::SourceFolder] + "\\" which is equal to the SourceFolder...

When I give JDEMember a value (also has the property EvaluateAsExpression = True; I set it to false on my machine) and create the corresponding subdirectory, the copy step works on my machine.

It then fails at the Delete step, but the reason there is that DestinationFileName is not a FileName but the name of a Directory. You'll have some work there to tune your variables, but I guess you know how to make this work now.

Regards,

Willem
http://wschampheleer.wordpress.com[/url]

dave-dj SSChampion Points: 11297 More actions · Answer 9

Thank You.

I'm guessing that I've inadventently changed something prior to copy to the production server.

I've gone back and looked at the variables again and made the following changes:

removed variable User::JDEMember

changed User::FileName expresssion from:

REPLACE(REPLACE(REPLACE( @[User::SourceFile] , @[User::SourceFolder] , "" ), ".txt", ".MBR"), "\\" , "")

to:

REPLACE(REPLACE( @[User::SourceFile] , @[User::SourceFolder] , "" ), ".txt", ".MBR")

and also change User::DestinationFileName

from

@[User::SourceFolder] + "\\" + @[User::JDEMember]

to:

@[User::SourceFolder] + "\\" + REPLACE(REPLACE(REPLACE( @[User::SourceFile] , @[User::SourceFolder] , "" ), ".txt", ".MBR"), "\\" , "")

switched DelayValidation to True for as I have blanked out the SourceFile variable value under package variables - so it prevents the validation errors.

- FEL Process Commtrac JDE Files

- FST Rename Source File

_____________________________________________________________________________MCITP: Business Intelligence Developer (2005)

dave-dj SSChampion Points: 11297 More actions · Answer 10

Thanks again for your help.

It's a package that evolved, which is where I think i managed to mix up the variables a little.

I can't understand how it worked for the original file, but not the new ones, but I think I'll go over the process flow for the files when I get a minute.

_____________________________________________________________________________MCITP: Business Intelligence Developer (2005)