File System Task Error - Process in use

Hi,

In the integration services package i'm working on i connect to an excel database and have two data flows which deal with two work sheets in the excel spreadsheet. The data flows do some transformation and then write the data out to a SQL database (two differnet tables). This is all working great however what i want to do once both data flows are complete is to move the directory which contains the current file that is being looped through. At present there is a foreach loop going through a number of directories. I have tried to implement the File System Task object to move the directory but get the following error:

[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.".

I've read a similar post where by the problem was due to not closing the excel connection before doing the File System Task. I cannot seem to find where i would be able to do this.

If anyone has any ideas i'm looking forward to hearing them.

Many thanks in advance,

Grant

i'm not sure what's causing your problem. however, my hunch is that a data flow task is still executing when control is passed to the file system task. you need make sure that both data flow tasks have completed before control is passed to the next task. one way to do this is to place both data flow tasks within a single sequence container, and then link this container to the next task. that way, control won't proceed to the next task until both data flow tasks have completed.

i hope this helps.

|||

Hi Duane,

Thanks for the response. I had the same thought that the task hadn't released the file when it came to the moving of the directory. I have tried the suggestion of using the sequence container but i still get the same result. The move directory task still thinks it is being used by another process.

Any further suggestion would be welcomed. I thinking there must be a way to get round this or i'm sure it could be a common problem.

Many Thanks,

Grant

|||Hi Duane,

Further to my last post. After some debugging i have found that it is the directory itself that seems to be in use by another process. I can remove the contents of the file no proble at all. Does this shed any more light on the matter?

Cheers,

Grant|||Hi,

I have found what i believe to be my problem and can't believe i didn't see it earlier. I'm doing all of my file and directory processing within a foreach loop. Hence when i come to moving the current directory i'm sure the foreach loop is still using the folder as it enumerated value. I need to be able to somehow call the move directory task outwith the loop so that it doesn't have control of the process. Could anyone suggest a way of doing this?

Thanks in advance.

Grant|||

if i understand you correctly, you want both data flows to finish before control proceeds to moving the files. also, you want this process to repeat once for each file that you have. i'm also assuming that you have two seperate directories of files that you want to process.

if my understanding above is correct, then you need to do the following: place two sequence containters inside a foreach loop container. connect an on success constraint from one sequence container to the other. there should be two seperate data flow tasks inside the first sequence container. only when this sequence container succeeds will control flow to the second sequence container. the second sequence container should contain two seperate file system tasks. only when this sequence container succeeds will control flow to the next iteration of the foreach loop container.

i hope this helps.

|||Hi,

I'll clarify what i have set up at present.

There is a root directory in which a number of directories are stored. Within each of these directories are two files (one CSV and one XLS). We can ignore the CSV file just now. I have two data flow tasks, one which extracts transforms and loads data from one worksheet into a SQL table and another which does a similar process but from a different worksheet and to a different SQL table. Upon completion of both these tasks (which are now in a Sequence container) i want to move the directory containing both the files to an archive location. regardless of whether the file system tasks are in a sequence container there is still an error relating to the directory move. It still believes that the process is in use.
I still think that because the foreach loop is looping through the directories looking for each XLS file it is taking a hold on the directory itself and will not release it until the current directory until the next iteration of the loop.

With this thought in mind i thought that i could do something at the beginning of the loop to remove the directory that was referenced in the proevious iteration (possibly set a variable at the end of each iteration). The only problem i encouter when i try this is that because the variable has to be used as a source/destination value i cannot have this as a blank value when starting i get errors when this is attempted. I need to really skip the move directory on the first iteration and add an additional one at the end of the foreach loop.

My other thought is to just copy the directories during the loop and delete them after the loop has completed. I am aware however that using this method i run the risk that if something causes the package to crashh mid way through it might duplicate directories between the processing and archive directories.

Does this make any more sense.

Cheers,

Grant|||I have the problem resolved.

I have added the move directory task to the start of the foreach loop. A script bypasses the task on the first loop as there isn't a previous folder to archive. The archive folder path is assigned to the variable later in the loop. The next iteration the loop then has a folder to move in the variable which works no problem. After the loop is finished one final move task is added to deal with the final processed folder.

The reason it wouldn't allow an empty string as a connection source to begin with was because the "Delay Validation" property was set to false. If this is set to true the problem doesn't exist.

Thanks for all your help on this matter.

Grant