rename excel sheet in SSIS using EPPlus

Question

I have been trying to get the following simple code work, but it raises and exception on using (var excelPackage = new ExcelPackage(finfo))

        string filepath = Convert.ToString(Dts.Variables["User::FileFullPath"].Value);


        if (File.Exists(filepath))
        {
            FileInfo finfo = new FileInfo(filepath);

            MessageBox.Show(filepath);
            try
            {
                using (var excelPackage = new ExcelPackage(finfo))
                {
                    ExcelWorksheet ws = excelPackage.Workbook.Worksheets[0];
                    ws.Name = "Sheet1";
                    excelPackage.Save();
                }
            }
            catch(Exception ex)
            {
                MessageBox.Show(ex.StackTrace, "Error", MessageBoxButtons.OK);
                Dts.Events.FireError(1, "Check File Status", ex.StackTrace, "", 0);
            }
            Dts.TaskResult = (int)ScriptResults.Success;
        }
        else
        {   
            Dts.TaskResult = (int)ScriptResults.Failure;
        }

The error is :

By commenting out some lines I figured the error is on using (var excelPackage = new ExcelPackage(finfo)) line. Can someone tell what I am doing wrong here, or how to better get a more detailed exception?

Answer 1

I had the same problem with SSIS.

You need to add the EPPlus.dll assembly to the Global Assembly Cache (GAC)

The EPPlus.dll assembly is not available to your code until you add it to the GAC using one of the following methods.

Please refer this link on how to add DLLs to GAC. http://www.sqlservercentral.com/articles/Integration+Services+(SSIS)/105432/