Files are always Read-only when Using EPPLUS to generate Excel file from Database table

Question

I'm using EPPLUS to generate Excel file from Database table but the file created stay on Read-only mode until the full SSIS process is stopped. I need to move the file after later in the process and this will always fail with the following message in SSIS:

[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.".

When I try to open the file in excel I got the "File in Use"

book1.xlsx is locked for editing by 'another user'. Open 'Read-Only' or click 'Notify' to open read-only and receive notification when the document is no longer in use. I hope you'll be able to help me.

Here is my code:

   public void Main()
    {
        try
        {
            String FilePath = Dts.Variables["$Package::DestinationFileName"].Value.ToString();
            String TableName = Dts.Variables["$Package::SourceTableName"].Value.ToString();
            String ConnStr = Dts.Variables["$Project::ConnStr_DataWarehouse"].Value.ToString();

            //SqlConnection Conn = (SqlConnection)(Dts.Connections["DW"].AcquireConnection(Dts.Transaction) as SqlConnection);
            using (SqlConnection Conn = new SqlConnection(ConnStr))
            {
                String Sql = "SELECT * FROM " + TableName;
                if (File.Exists(FilePath))
                {
                    try { File.Delete(FilePath); }
                    catch (Exception ex) { MessageBox.Show(ex.Message.ToString()); Dts.TaskResult = (int)ScriptResults.Failure; }
                }
                using (DataTable dt = new DataTable())
                {
                    using (SqlCommand cmd = new SqlCommand(Sql, Conn))
                    {
                        Conn.Open();
                        using (SqlDataAdapter da = new SqlDataAdapter(cmd))
                        {
                            da.Fill(dt);
                            FileInfo newFile = new FileInfo(FilePath);
                            using (ExcelPackage p = new ExcelPackage(newFile))
                            {
                                using (ExcelWorksheet ws = p.Workbook.Worksheets.Add("RejectetionReport"))
                                {
                                    ws.Cells["A1"].LoadFromDataTable(dt, true);
                                    p.Save();
                                }
                            }
                        }
                        Conn.Close();
                    }
                } 
            }

            Dts.TaskResult = (int)ScriptResults.Success;
        }
        catch (Exception ex)
        {
            MessageBox.Show(ex.Message.ToString());
            Dts.TaskResult = (int)ScriptResults.Failure;
        }
    }

I'm using EPPLUS 4.0.5 which I plan to update to 4.1 but the release note doesn't seam to cover this issue.

EDIT: I've upgraded to 4.1 but the issue still exists.

this seems to be related to a long reported issue: https://epplus.codeplex.com/workitem/14919 — Kilren, Jul 22 '16 at 12:32
looking at your code and the epplus documentation looks like you are doing what you need to, but perhaps epplus has a bug that the using {} block isn't properly disposing of the objects. You could try to dispose of yourself and eliminate using.... — Matt, Jul 22 '16 at 21:26
I obviously already tried to dispose() manually of all disposable objects. I'll try to set object to null and see... — Kilren, Jul 25 '16 at 07:33
No luck with setting null neither. To me it sounds like a bug in the dispose of an EPPLUS object. But I'm still to find a workaround... — Kilren, Jul 25 '16 at 07:55

score 1 · Accepted Answer · answered Jul 25 '16 at 11:49

1

I found the issue. There is a bug in the EPPLUS library which is not disposing of a stream before disposal of the package. I've submitted a fix an pull request under fork DebugPackageDispose

Hopefully, this will be integrated soon.

answered Jul 25 '16 at 11:49

Kilren

395
4
12

Files are always Read-only when Using EPPLUS to generate Excel file from Database table

1 Answers1