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

Question

I have following Dataflow:

Control:

I just wanted to copy all the data from flatfiles in sourcefolder to sql database and after copying move those files to folder named Done.

But when i run this, i get 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.".

Data gets copied to sqlserver , but file does not moves.

My process tab is as follows:

Solution 1

This may be too obvious, but have you tried inserting a short delay to give the dataflow time to let go of the file? For example, insert an Execute SQL Task with contents like:

-- Wait for 1 second
WAITFOR DELAY '00:00:01'

Alternatively, you could handle the Failure Path and retry, perhaps after a delay.

Solution 2

If the messages cites your ".ispac" file, you have an unclosed debug. Enter Task Manager and close the Debug Host.

Solution 3

I found this link by accident and posting this to help others that get here as well.

When using a Script task make sure you drop connection with Close() or use connection inside a USING().

The connection is held after the task is complete and until the whole package is complete unless you do either of the above.

Solution 4

My solution:

1. Go to Task Manager
2. Details Tab. enter image description here
3. Locate the process “DtsDebugHost.exe“.
4. Kill this process. There might be multiple instances of this process. Kill all of them.
5. Reexecute SSIS package

Solution 5

If you are using an Excel connection, use the below code (C#) in a Script task to close all Excel processes, before you attempt to move/rename the file.

System.Diagnostics.Process[] proc=System.Diagnostics.Process.GetProcessesByName("Excel");
foreach (System.Diagnostics.Process p in proc)
{
    if (!string.IsNullOrEmpty(p.ProcessName))
    {
        try
        {
            p.Kill();
        }
        catch { }
    }
}

View more solutions

Comments

[Netricity]

The Debug Host's Image Name in Windows Task Manager is "DtsDebugHost.exe" and Description is "SSIS Debug Host"

[Roman M]

That's a good one, sir! In my case it was dtexec.exe process I had to kill PS> get-process dtexec.exe | stop-process -force