SSIS - The process cannot access the file because

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:

Answer 1

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

Answer 2

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 { }
    }
}

Answer 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.

Answer 4

for me this error has nothing to do with SSIS and occurred after end tasking not responding BIDS

one of my excel files was get locked by BIDS debugger(DtsDebugHost.exe) which closing BIDS does not helps.

finally using http://filehippo.com/download_unlocker/ shows me actual locker and also simply remove it.

please note that BIDS needs to be restarted after unlocking DtsDebugHost.exe

Answer 5

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.

Answer 6

I use this function in tandem with a wait routine:

public bool IsFileLocked(string filePath)
{
    try
    {
        //Open the file exclusively
        using (File.Open(filePath, FileMode.Open, FileAccess.Read, FileShare.None)) { }
    }
    catch (IOException e)
    {
        var errorCode = Marshal.GetHRForException(e) & ((1 << 16) - 1);
        return errorCode == 32 || errorCode == 33;
    }

    return false;
}