EPPlus 2.9.0.1 throws System.IO.IsolatedStorage.IsolatedStorageException when trying to save a file bigger than ~1.5 MiB from a SSIS package

The problem

When I try to save a file over ~1.5 MiB with EPPlus ExcelPackage.Save() throws a System.IO.IsolatedStorage.IsolatedStorageException.

The explanation

I'm creating a SSIS package with Visual Studio 2008 9.0.30729.4462 QFE and .NET Framework 3.5 SP1 to export the content of a SQL Server 2008 SP2 10.0.4311.0 64 bit table through the EPPlus 2.9.0.1 library.

The SSIS package is really simple: an Execute SQL Task which reads the table's content and puts it in a variable followed by a Script task which reads the recordset variable and saves the content to disk through EPPlus.

The code of the Script Task is:

namespace ST_00a0b40814db4c7290b71f20a45b62c6.csproj
{
    using System;
    using System.AddIn;
    using System.Data;
    using System.Data.OleDb;
    using System.IO;
    using Microsoft.SqlServer.Dts.Runtime;
    using Microsoft.SqlServer.Dts.Tasks.ScriptTask;
    using OfficeOpenXml;

    [AddIn("ScriptMain", Version = "1.0", Publisher = "", Description = "")]
    public partial class ScriptMain : VSTARTScriptObjectModelBase
    {
        public void Main()
        {
            DataTable documentList = new DataTable();
            using (OleDbDataAdapter adapter = new OleDbDataAdapter())
            {
                adapter.Fill(documentList, this.Dts.Variables["DocumentList"].Value);
            }
            if (documentList.Rows.Count > 0)
            {
                FileInfo fileInfo = new FileInfo(@"C:\Temp\Test.xlsx");
                if (fileInfo.Exists)
                {
                    fileInfo.Delete();
                }
                using (ExcelPackage package = new ExcelPackage(fileInfo))
                {
                    ExcelWorksheet worksheet = package.Workbook.Worksheets.Add("Documents");
                    for (Int32 i = 0; i < documentList.Rows.Count; i++)
                    {
                        for (Int32 j = 0; j < documentList.Columns.Count; j++)
                        {
                            worksheet.Cells[i + 1, j + 1].Value = documentList.Rows[i][j];
                        }
                    }
                    package.Save();
                }
            }
            Dts.TaskResult = Convert.ToInt32(DTSExecResult.Success);
        }
    }
}

When I feed the Script Task only a couple of records the package runs fine, but when I run it against the full table the package.Save(); blows up with a System.IO.IsolatedStorage.IsolatedStorageException: Unable to determine the identity of domain exception.

Here you can see the full stack trace:

Error: System.Reflection.TargetInvocationException: Exception has been thrown by the target of an invocation. ---> System.InvalidOperationException: Error saving file C:\Temp\Test.xls ---> System.IO.IsolatedStorage.IsolatedStorageException: Unable to determine the identity of domain.
   at System.IO.IsolatedStorage.Isol开发者_C百科atedStorage._GetAccountingInfo(Evidence evidence, Type evidenceType, IsolatedStorageScope fAssmDomApp, Object& oNormalized)
   at System.IO.IsolatedStorage.IsolatedStorage.GetAccountingInfo(Evidence evidence, Type evidenceType, IsolatedStorageScope fAssmDomApp, String& typeName, String& instanceName)
   at System.IO.IsolatedStorage.IsolatedStorage._InitStore(IsolatedStorageScope scope, Evidence domainEv, Type domainEvidenceType, Evidence assemEv, Type assemblyEvidenceType, Evidence appEv, Type appEvidenceType)
   at System.IO.IsolatedStorage.IsolatedStorage.InitStore(IsolatedStorageScope scope, Type domainEvidenceType, Type assemblyEvidenceType)
   at System.IO.IsolatedStorage.IsolatedStorageFile.GetStore(IsolatedStorageScope scope, Type domainEvidenceType, Type assemblyEvidenceType)
   at System.IO.IsolatedStorage.IsolatedStorageFile.GetUserStoreForDomain()
   at MS.Internal.IO.Packaging.PackagingUtilities.ReliableIsolatedStorageFileFolder.GetCurrentStore()
   at MS.Internal.IO.Packaging.PackagingUtilities.ReliableIsolatedStorageFileFolder..ctor()
   at MS.Internal.IO.Packaging.PackagingUtilities.GetDefaultIsolatedStorageFile()
   at MS.Internal.IO.Packaging.PackagingUtilities.CreateUserScopedIsolatedStorageFileStreamWithRandomName(Int32 retryCount, String& fileName)
   at MS.Internal.IO.Packaging.SparseMemoryStream.EnsureIsolatedStoreStream()
   at MS.Internal.IO.Packaging.SparseMemoryStream.SwitchModeIfNecessary()
   at MS.Internal.IO.Packaging.SparseMemoryStream.Write(Byte[] buffer, Int32 offset, Int32 count)
   at MS.Internal.IO.Packaging.CompressEmulationStream.Write(Byte[] buffer, Int32 offset, Int32 count)
   at MS.Internal.IO.Packaging.CompressStream.Write(Byte[] buffer, Int32 offset, Int32 count)
   at MS.Internal.IO.Zip.ProgressiveCrcCalculatingStream.Write(Byte[] buffer, Int32 offset, Int32 count)
   at MS.Internal.IO.Zip.ZipIOModeEnforcingStream.Write(Byte[] buffer, Int32 offset, Int32 count)
   at System.IO.StreamWriter.Flush(Boolean flushStream, Boolean flushEncoder)
   at System.IO.StreamWriter.Write(String value)
   at System.IO.TextWriter.Write(String format, Object arg0, Object arg1)
   at OfficeOpenXml.ExcelWorksheet.UpdateRowCellData(StreamWriter sw)
   at OfficeOpenXml.ExcelWorksheet.SaveXml()
   at OfficeOpenXml.ExcelWorksheet.Save()
   at OfficeOpenXml.ExcelWorkbook.Save()
   at OfficeOpenXml.ExcelPackage.Save()
   --- End of inner exception stack trace ---
   at OfficeOpenXml.ExcelPackage.Save()
   at ST_00a0b40814db4c7290b71f20a45b62c6.csproj.ScriptMain.Main() in C:\Temp\ScriptMain.cs:line 39
   --- End of inner exception stack trace ---
   at System.RuntimeMethodHandle._InvokeMethodFast(Object target, Object[] arguments, SignatureStruct& sig, MethodAttributes methodAttributes, RuntimeTypeHandle typeOwner)
   at System.RuntimeMethodHandle.InvokeMethodFast(Object target, Object[] arguments, Signature sig, MethodAttributes methodAttributes, RuntimeTypeHandle typeOwner)
   at System.Reflection.RuntimeMethodInfo.Invoke(Object obj, BindingFlags invokeAttr, Binder binder, Object[] parameters, CultureInfo culture, Boolean skipVisibilityChecks)
   at System.Reflection.RuntimeMethodInfo.Invoke(Object obj, BindingFlags invokeAttr, Binder binder, Object[] parameters, CultureInfo culture)
   at System.RuntimeType.InvokeMember(String name, BindingFlags bindingFlags, Binder binder, Object target, Object[] providedArgs, ParameterModifier[] modifiers, CultureInfo culture, String[] namedParams)
   at System.Type.InvokeMember(String name, BindingFlags invokeAttr, Binder binder, Object target, Object[] args, CultureInfo culture)
   at Microsoft.SqlServer.Dts.Tasks.ScriptTask.VSTATaskScriptingEngine.ExecuteScript()

I've been able to pinpoint the issue to the size of the generated file: when the size of the Excel file grows about 1.5 MiB (this is a more or less value, I've not been able to find the exact size), the error shows up.

The only information I've been able to find on the net is a blog post, there the blogger proposes a solution where he "outsources" the code to a DLL and uploads it to the server's GAC and then runs the following code:

AppDomainSetup setup = new AppDomainSetup();
setup.ApplicationBase = rootPath;
setup.DisallowBindingRedirects = false;
setup.DisallowCodeDownload = true;
setup.ConfigurationFile = AppDomain.CurrentDomain.SetupInformation.ConfigurationFile;

Evidence evidence = new Evidence();
evidence.AddHost(new Zone(SecurityZone.MyComputer));

AppDomain ad = AppDomain.CreateDomain("NewAppDomain", evidence, setup);

YourClass yourClass = (YourClass)ad.CreateInstanceAndUnwrap(typeof(YourClass).Assembly.FullName, typeof(YourClass).FullName);

yourClass.aMethod();

AppDomain.Unload(ad);

However, I can't try this solution because I have no access to the server's GAC and I can't have the DLL uploaded.

Is there some other way to bypass this issue?

I also opened a bug report on EPPlus' issue tracker on the matter.

The summary

• SSIS package
• Visual Studio 2008 9.0.30729.4462 QFE
• .NET Framework 3.5 SP
• SQL Server 2008 SP2 10.0.4311.0 64 bit
• EPPlus 2.9.0.1
• Big output file
• System.IO.IsolatedStorage.IsolatedStorageException: Unable to determine the identity of domain

It turns out that you don't need to create an entirely new DLL and upload it to the GAC to get this to work. You can just create a new instance of your original class (or a new class with the methods you need to call) in a different AppDomain.

1) Take the code that causes the exception and place it in a seperate method. Make sure that all objects that will be passed in or out are either Serializable or extend MarshalByRefObject. In your case:

public void SavePackage(FileInfo fileInfo, DataTable documentList)
{
    using (ExcelPackage package = new ExcelPackage(fileInfo))
    {
        ExcelWorksheet worksheet = package.Workbook.Worksheets.Add("Documents");
        for (Int32 i = 0; i < documentList.Rows.Count; i++)
        {
            for (Int32 j = 0; j < documentList.Columns.Count; j++)
            {
                worksheet.Cells[i + 1, j + 1].Value = documentList.Rows[i][j];
            }
        }
        package.Save();
    } 
}

2) Create a new instance of your class using the code you mentioned above.

AppDomainSetup setup = new AppDomainSetup();
setup.ApplicationBase = AppDomain.CurrentDomain.BaseDirectory;

Evidence evidence = new Evidence(AppDomain.CurrentDomain.Evidence);
evidence.AddAssembly(Assembly.GetExecutingAssembly().FullName);
evidence.AddHost(new Zone(SecurityZone.MyComputer));

AppDomain ad = AppDomain.CreateDomain(DomainName, evidence, setup);
ScriptMain mainClass = (ScriptMain)ad.CreateInstanceAndUnwrap(typeof(ScriptMain).Assembly.FullName, typeof(ScriptMain).FullName);      

3) Call the method, then unload the AppDomain.

try
{
    mainClass.SavePackage(fileInfo, documentList);
}
finally
{
    AppDomain.Unload(ad);  
}