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:
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.
If the messages cites your ".ispac" file, you have an unclosed debug. Enter Task Manager and close the Debug Host.
My solution:
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.
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.
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 { }
}
}
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.
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
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;
}
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
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
You can easily "File System Task" component.
You can pass the name of your current file as a parameter to "File System Task" component
I tested it myself and everything was ok!
© 2022 - 2024 — McMap. All rights reserved.