Flat File Creation-Script Task----Flatfile Destination [85]: The process cannot access the file because it is being used by another process

Question

Hi,
    I am creating a text file using script task and write a header row.

At the next Dataflow task, I am opening the same file to write the table content. Here I am getting the error

Flatfile Destination [85]: The process cannot access the file because it is being used by another process

Suggestions pls....

---

http://effulgentlogs.blogspot.com

Answer 1

strange. Can you share your package?

---

http://sqlblog.com/blogs/jamie_thomson/ | http://jamiethomson.spaces.live.com/ | @jamiet

Answer 2

This must issue is with the code inside the script tasks.
Inside the script tasks you must be using a object to connectto the text file, hold the text file and write the header into it. Kindly check whether you closed the objects connection which will disconnect and save the text file.

If you have already done please share the code of the script tasks if it is possible.

---

Thanks, Bharani M - Please mark the post as answered if it answers your question. - Please vote the post as Helpful if you find the post as helpful.

Answer 3

Yes, I had closed/disposed both filestream and streamwriter. I will share the code ASAP.

---

http://effulgentlogs.blogspot.com

Answer 4

Sure post whenever is possible.
Also,
ProcessMonitor will tell you what is using the file. With this you can narrow down to the location where it is getting locked by a process.

---

Thanks, Bharani M - Please mark the post as answered if it answers your question. - Please vote the post as Helpful if you find the post as helpful.

Answer 5

strDateT = DateTime.Now.ToString()
strDateT = strDateT.Replace("/", "")
strDateT = strDateT.Replace(":", "")
strHeader = "HDR|MCS_QAD_" & strDateT & ".txt|" & DateTime.Now.ToShortDateString
Dts.Variables("varFileName").Value = "C:\MCSLoad\MCS_" & strDateT & ".txt"
Dim fs As New FileStream("C:\\MCSLoad\\MCS_" & strDateT & ".txt", FileMode.CreateNew, FileAccess.Write)
Dim sw As New StreamWriter(fs)
sw.WriteLine(strHeader)
fs = Nothing
Dts.TaskResult = Dts.Results.Success

Here is my script task code. I am storing this path in a variable.

Next, I had created a data flow task which inserts records from a table to the same file. Here the problem occurs.

---

http://effulgentlogs.blogspot.com

Answer 6

Tried this also

        strDateT = DateTime.Now.ToString()
        strDateT = strDateT.Replace("/", "")
        strDateT = strDateT.Replace(":", "")
        strHeader = "HDR|MCS_" & strDateT & ".txt|" & DateTime.Now.ToShortDateString
        Dts.Variables("varFileName").Value = "C:\MCSLoad\MCS_" & strDateT & ".txt"
        Dim fs As New FileStream("C:\\MCSLoad\\MCS_QAD_" & strDateT & ".txt", FileMode.CreateNew, FileAccess.Write)
        Dim sw As New StreamWriter(fs)
        sw.WriteLine(strHeader)
        sw.Close()
        fs.Close()
        fs.Dispose()
        Dts.TaskResult = Dts.Results.Success

---

http://effulgentlogs.blogspot.com

Answer 7

You need to close the StreamWriter pointing to your file before you can open the file again. Try adding below line of code before fs = Nothing

sw.Close

It seems to work for me all fine.

Hope that helps!!

Cheers!!

M.

Answer 8

Tried this also

        strDateT = DateTime.Now.ToString()
        strDateT = strDateT.Replace("/", "")
        strDateT = strDateT.Replace(":", "")
        strHeader = "HDR|MCS_" & strDateT & ".txt|" & DateTime.Now.ToShortDateString
        Dts.Variables("varFileName").Value = "C:\MCSLoad\MCS_" & strDateT & ".txt"
        Dim fs As New FileStream("C:\\MCSLoad\\MCS_QAD_" & strDateT & ".txt", FileMode.CreateNew, FileAccess.Write)
        Dim sw As New StreamWriter(fs)
        sw.WriteLine(strHeader)
        sw.Close()
        fs.Close()
        fs.Dispose()
        Dts.TaskResult = Dts.Results.Success

---

http://effulgentlogs.blogspot.com

Few things to note:

1. You have a space character in the .txt filename that you are creating. The space is between the date and the time part of it. Have a look.

2. You'll need to add the  varFileName package variable to the ReadWriteVariables collection of the package.

Below is the script I used in my test package and it worked all fine ( I used C# tha VB .Net):-

public void Main()

        {

            // TODO: Add your code here

            String strDateT;

            string strHeader;

        strDateT = DateTime.Now.ToString();

        strDateT = strDateT.Replace("/", "");

        strDateT = strDateT.Replace(":", "");

        strDateT = strDateT.Replace(" ", ""); /* This is to remove the space character in your filename.*/

        strHeader = "HDR|MCS_QAD_" + strDateT + ".txt|" + DateTime.Now.ToShortDateString();

        MessageBox.Show(strDateT);

        MessageBox.Show(strHeader);

        Dts.Variables["varFileName"].Value = "D:\\Temp\\SSISTraining\\MCS_" + strDateT + ".txt";

        FileStream fs = new  FileStream("D:\\Temp\\SSISTraining\\MCS_" + strDateT + ".txt", FileMode.CreateNew, FileAccess.Write);

        StreamWriter sw = new  StreamWriter(fs);

        sw.WriteLine(strHeader);

       

        sw.Close();

        fs.Close();

        fs = null;

        Dts.TaskResult = (int)ScriptResults.Success;

        }

Hope this helps.

Cheers!!

M.

Answer 9

Yesterday itself i had tried removing the spaces.

The problem is that the flat file was accesed by some other resources so that in the next step (Dataflow task), i was unable to insert record from a table.

If i am executing the script task only, I don't had any problem

Here is the error message,

[QAD Flatfile ErrRecords [153]] Warning: The process cannot access the file because it is being used by another process.

Clear now?

---

http://effulgentlogs.blogspot.com

Answer 10

Manikandan,
Did you try checkin the lock on the file using the ProcessMonitor?

---

Thanks, Bharani M - Please mark the post as answered if it answers your question. - Please vote the post as Helpful if you find the post as helpful.

Answer 11

This looks like a Warning than an error message. I am still not clear on what issue you are facing. Is this on design time or on execution of your package?

Someone accessing yesterday's file shouldn't fail your package because your code is generating a new file even for a difference of milliseconds!! If you are seeing this message at design time, set the DelayValidation property to True. If this appears at run time, try executing the code I have posted above and see where it takes you.

Cheers!!#M.

Answer 12

Yes thats the warning and here is the error message next to that warning.

[QAD Flatfile ErrRecords [153]] Error: Cannot open the datafile "C:\MCSLoad\MCS_QAD_119200924550PM.txt".

---

http://effulgentlogs.blogspot.com

Answer 13

Here is my package

http://www.yousendit.com/download/TzY3RkJRT01vQUtGa1E9PQ

---

http://effulgentlogs.blogspot.com

Answer 14

Looked at your package. The problem with your package is that you are writing to the same flat file (QAD_Flatfile connection) for Error as well we Success situations in the same data flow task. You need to write to two different flat files in your package. That way, the flat files won't get tied up and package will execute successfully. 

I tested both these scenarios using yor package and writing to two different flat files worked just fine.

Cheers!!

M. 

Answer 15

Thanks Muqadder. I don't know how i missed this simple mistake that i had done.

Thank u.

---

http://effulgentlogs.blogspot.com