CODEWITHSUNDEEP
c#.netsql-serverssis
C Sharper
C Sharper

Reputation: 8626

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

I have following Dataflow:

enter image description here

Control:

enter image description here

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:

enter image description here

Upvotes: 13

Views: 58091

Answers (11)

Shahidul Islam Molla
Shahidul Islam Molla

Reputation: 640

My solution:

  1. Go to Task Manager
  2. Details Tab.
  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

Upvotes: 8

Paulie D
Paulie D

Reputation: 21

In my case, I had previously opened a Flat File with System.IO.StreamReader but, after processing, forgot to close the Reader!


    Dim oReader As New StreamReader(sPathAndFileName)
    ...
    oReader.Close()
    oReader = Nothing

Upvotes: 0

shanky
shanky

Reputation: 396

Add a script task executing below mentioned lines, before doing file operation:

Public Sub Main()

Dim procList() As Process = Process.GetProcesses()

Dim k As Integer

For k = 0 To procList.GetUpperBound(0) Step k + 1

If procList(k).ProcessName = "EXCEL" Then

procList(k).Close()

procList(k).Dispose()

End If

Next

GC.Collect()

GC.WaitForPendingFinalizers()

Dts.TaskResult = ScriptResults.Success

End Sub

Upvotes: 0

Saman Saran
Saman Saran

Reputation: 1

You can easily "File System Task" component.

Image of Your Control Flow:

You can pass the name of your current file as a parameter to "File System Task" component

Image for File System Task Setting

I tested it myself and everything was ok!

Upvotes: -1

Ajay Meda
Ajay Meda

Reputation: 378

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

Upvotes: 3

GeorgiG
GeorgiG

Reputation: 1103

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

Upvotes: 12

KeithL
KeithL

Reputation: 5594

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.

Upvotes: 7

MezTemplar
MezTemplar

Reputation: 21

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

Upvotes: 1

Iman
Iman

Reputation: 18906

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

Upvotes: 1

Saim Saboor
Saim Saboor

Reputation: 29

If you are using Excel connection from SSIS connection Manager. There is a property RetainSameConnection. It is true by default. Make it False and you will be all set.

Upvotes: 2

criticalfix
criticalfix

Reputation: 2870

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.

Upvotes: 5

Related Questions