Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


SSIS File Rename - The process cannot access the file because it is being used by another process


SSIS File Rename - The process cannot access the file because it is being used by another process

Author
Message
ipisors
ipisors
SSC Rookie
SSC Rookie (39 reputation)SSC Rookie (39 reputation)SSC Rookie (39 reputation)SSC Rookie (39 reputation)SSC Rookie (39 reputation)SSC Rookie (39 reputation)SSC Rookie (39 reputation)SSC Rookie (39 reputation)

Group: General Forum Members
Points: 39 Visits: 66
Current setup:

Foreach Loop container, containing the following items in sequence:
1. Date Flow task, containing Flat File source on network share, then OLE DB destination (SQL table).
2. Script task, which increments some variables
3. Script task which I just added in an attempt to solve this, which simply waits for 5 seconds. Threading.Thread.Sleep(5000)
4. File system task, which renames file (moves).

Yesterday this process worked fine several times, but now I get "The process cannot access the file because it is being used by another process".

I'm 100% positive that the SQL agent service account has full read/write/delete access to the file share; it always has and in fact this file share is shared almost publicly. I've also checked out the (2) text files that it is looping through, and I have no problem cutting and pasting them myself - they definitely aren't in use by any other process.

The Foreach container is probably executing extremely fast, because at this time the files have zero records - just headers.

What else can I do here ??
Attachments
screenshot1.png (17 views, 17.00 KB)
Jack Corbett
  Jack Corbett
SSChampion
SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)

Group: General Forum Members
Points: 11014 Visits: 14858
When I get this error it's usually because I've opened the file in Excel/NotePad/PickYourEditor that has a lock on the file.

How are the files being created? Is the create process running too?



Jack Corbett

Applications Developer

Don't let the good be the enemy of the best. -- Paul Fleming
At best you can say that one job may be more secure than another, but total job security is an illusion. -- Rod at work

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
How to Post Performance Problems
Crosstabs and Pivots or How to turn rows into columns Part 1
Crosstabs and Pivots or How to turn rows into columns Part 2
ipisors
ipisors
SSC Rookie
SSC Rookie (39 reputation)SSC Rookie (39 reputation)SSC Rookie (39 reputation)SSC Rookie (39 reputation)SSC Rookie (39 reputation)SSC Rookie (39 reputation)SSC Rookie (39 reputation)SSC Rookie (39 reputation)

Group: General Forum Members
Points: 39 Visits: 66
The files are being created at a single point in time, daily about 4:00 AM. I'm running my process way after that. I don't have the files open, and in fact I've ensured that I'm able to manually cut & paste them so nothing else is holding them. As far as that line of thought I feel 100% positive it's not that.

Since the time I posted this I've tried all kinds of other things, the most recent being:

1. script task to create a temp directory, copy all the files into the temp dir.
2. foreach file enumerator that goes through TEMP directory instead of 'real' directory
3. script task that renames (moves) them from the real directory to an archive.

Just run into an error every which-way, it seems. Carrying on with my attempts...
Jack Corbett
  Jack Corbett
SSChampion
SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)

Group: General Forum Members
Points: 11014 Visits: 14858
Since this is out on a file share you could look at the open files in computer management connected to that server to see what has the file(s) open.



Jack Corbett

Applications Developer

Don't let the good be the enemy of the best. -- Paul Fleming
At best you can say that one job may be more secure than another, but total job security is an illusion. -- Rod at work

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
How to Post Performance Problems
Crosstabs and Pivots or How to turn rows into columns Part 1
Crosstabs and Pivots or How to turn rows into columns Part 2
ipisors
ipisors
SSC Rookie
SSC Rookie (39 reputation)SSC Rookie (39 reputation)SSC Rookie (39 reputation)SSC Rookie (39 reputation)SSC Rookie (39 reputation)SSC Rookie (39 reputation)SSC Rookie (39 reputation)SSC Rookie (39 reputation)

Group: General Forum Members
Points: 39 Visits: 66
Thanks, good tip. I'll see if I can get "IT" to help me out in that regard. w00t
Eirikur Eiriksson
Eirikur Eiriksson
SSCertifiable
SSCertifiable (6.7K reputation)SSCertifiable (6.7K reputation)SSCertifiable (6.7K reputation)SSCertifiable (6.7K reputation)SSCertifiable (6.7K reputation)SSCertifiable (6.7K reputation)SSCertifiable (6.7K reputation)SSCertifiable (6.7K reputation)

Group: General Forum Members
Points: 6693 Visits: 17686
Quick thought, grab the SysInternals utility which shows open files, cannot remember the name but should be easy to find. It will show if any process is accessing the file. I've come across this problem quite few times, most of the time SSIS was the culprit, not closing the files after simple operations like row counting etc.
Cool
ipisors
ipisors
SSC Rookie
SSC Rookie (39 reputation)SSC Rookie (39 reputation)SSC Rookie (39 reputation)SSC Rookie (39 reputation)SSC Rookie (39 reputation)SSC Rookie (39 reputation)SSC Rookie (39 reputation)SSC Rookie (39 reputation)

Group: General Forum Members
Points: 39 Visits: 66
OK thanks.

1) How would I Pause/break the SSIS package at the specific time to see what has the files open? In the middle of the Foreach file enumerator? Or right after that data flow task, and how?

2) If I find SSIS has the files open, how do I solve this and tell SSIS to close them? Is the only solution to have multiple, separate packages?

Goodness, this was so much easier in 100% VBA.
Eirikur Eiriksson
Eirikur Eiriksson
SSCertifiable
SSCertifiable (6.7K reputation)SSCertifiable (6.7K reputation)SSCertifiable (6.7K reputation)SSCertifiable (6.7K reputation)SSCertifiable (6.7K reputation)SSCertifiable (6.7K reputation)SSCertifiable (6.7K reputation)SSCertifiable (6.7K reputation)

Group: General Forum Members
Points: 6693 Visits: 17686
Within the SysInternals utility you can filter the output on a folder, the file handle will show up there during the Data Flow task execution. The question is whether SSIS releases the handle before the next file system operation on that file.
Cool
ipisors
ipisors
SSC Rookie
SSC Rookie (39 reputation)SSC Rookie (39 reputation)SSC Rookie (39 reputation)SSC Rookie (39 reputation)SSC Rookie (39 reputation)SSC Rookie (39 reputation)SSC Rookie (39 reputation)SSC Rookie (39 reputation)

Group: General Forum Members
Points: 39 Visits: 66
I was unable to ascertain to a certainty when the package lets the files go. Either way, the fact that they were being locked was indisputable I guess.

Adding any one of the several creative methods to "wait" did no good either. The files were locked until the package execution was finished, it seemed.

I eventually solved my problem like this:

1. in the ForEach loop, after processing the file into SQL table, I simply COPIED the file...That worked, locks notwithtanding.

2. in the same loop, execute a stored procedure to insert a value into a table saying the file was Processed, not yet Deleted.

3. create a new package running independently from the first one (but slightly after it each morning), that opens an data flow (ado source - files not yet deleted, recordset destination), and loops through it, updating a variable and using a simple script task to delete the file. and executing a SP to say file is now deleted. (yes there is the Filesystem delete file task, but it's so buggy in terms of Source and Variables that it's easier to just write a 3-line script task..)

Works like a champ, I guess I'm OK with this for now. Have researched this SSIS file locking thing to no end, and none of the posted solutions have worked for me...(Waits, Separating tasks/steps, Separating foreach containers, etc).....But there is a group of people who seem to have issues still and no solution; guess I'm in there with them.
Eirikur Eiriksson
Eirikur Eiriksson
SSCertifiable
SSCertifiable (6.7K reputation)SSCertifiable (6.7K reputation)SSCertifiable (6.7K reputation)SSCertifiable (6.7K reputation)SSCertifiable (6.7K reputation)SSCertifiable (6.7K reputation)SSCertifiable (6.7K reputation)SSCertifiable (6.7K reputation)

Group: General Forum Members
Points: 6693 Visits: 17686
Good stuff, glad you got this resolved.
This is less of a problem on 2K8 R2 and later, not certain why, just my experience. As a rule of thumb, I try to use script tasks for all non-data operations rather than the File System Tasks, one can close the file for further operations without having to rely on the SSIS behaviour.
Cool
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search