IsloatedFileStorage Exception in SSIS Script task while using Openxml - Unable to determine domain

Question

I'm having an SSIS script task that creates an Excel file using openxml. I recieve the following exception while my pacakge is running.

Error: System.Reflection.TargetInvocationException: Exception has been thrown by the target of an invocation. ---> System.IO.IsolatedStorage.IsolatedStorageException: Unable to determine the identity of domain.
   at System.IO.IsolatedStorage.IsolatedStorage._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 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.Xml.XmlUtf8RawTextWriter.FlushBuffer()
   at System.Xml.XmlUtf8RawTextWriter.WriteAttributeTextBlock(Char* pSrc, Char* pSrcEnd)
   at System.Xml.XmlUtf8RawTextWriter.WriteString(String text)
   at System.Xml.XmlWellFormedWriter.WriteString(String text)
   at DocumentFormat.OpenXml.OpenXmlElement.WriteAttributesTo(XmlWriter xmlWriter)
   at DocumentFormat.OpenXml.OpenXmlElement.WriteTo(XmlWriter xmlWriter)
   at DocumentFormat.OpenXml.OpenXmlCompositeElement.WriteContentTo(XmlWriter w)
   at DocumentFormat.OpenXml.OpenXmlElement.WriteTo(XmlWriter xmlWriter)
   at DocumentFormat.OpenXml.OpenXmlCompositeElement.WriteContentTo(XmlWriter w)
   at DocumentFormat.OpenXml.OpenXmlElement.WriteTo(XmlWriter xmlWriter)
   at DocumentFormat.OpenXml.OpenXmlCompositeElement.WriteContentTo(XmlWriter w)
   at DocumentFormat.OpenXml.OpenXmlPartRootElement.WriteTo(XmlWriter xmlWriter)
   at DocumentFormat.OpenXml.OpenXmlPartRootElement.SaveToPart(OpenXmlPart openXmlPart)
   at DocumentFormat.OpenXml.OpenXmlPartRootElement.Save()
   at DocumentFormat.OpenXml.Packaging.OpenXmlPackage.SavePartContents()
   at DocumentFormat.OpenXml.Packaging.OpenXmlPackage.Dispose(Boolean disposing)
   at DocumentFormat.OpenXml.Packaging.OpenXmlPackage.Dispose()
   at ST_b7ca688d916f485d9175583064912d2f.csproj.ScriptMain.BuildExel(String fileName)
   at ST_b7ca688d916f485d9175583064912d2f.csproj.ScriptMain.Main()
   --- 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()

And i believe i receieve the exception while trying to save the excel -

myWorkbook.WorkbookPart.Workbook.Save();

Can anyone suggest a solution for this.?

Answer 1

Hi Binoykp,

Thanks for posting in the MSDN Forum.

It's based on my experience that we aren't able to give you any suggestions until us show me detailed script snippet for trouble shooting.

Have a good day,

Tom

---

Tom Xu [MSFT]
MSDN Community Support | Feedback to us

Answer 2

Hai Tom,

Please find below the BuildExcel method inside the script task. This is the only method i'm invoking in Main()

public

void BuildExel(string fileName)

{

using (SpreadsheetDocument myWorkbook = SpreadsheetDocument.Create(fileName, SpreadsheetDocumentType.Workbook))

{

// Workbook Part

WorkbookPart workbookPart = myWorkbook.AddWorkbookPart();

var worksheetPart = workbookPart.AddNewPart<WorksheetPart>();

string relId = workbookPart.GetIdOfPart(worksheetPart);

// File Version

var fileVersion = new FileVersion { ApplicationName = "Microsoft Office Excel" };

// Style Part

WorkbookStylesPart wbsp = workbookPart.AddNewPart<WorkbookStylesPart>();

wbsp.Stylesheet = CreateStylesheet();

wbsp.Stylesheet.Save();

// Sheets

var sheets = new Sheets();

var sheet = new Sheet { Name = "Payment_Info", SheetId = 1, Id = relId };

sheets.Append(sheet);

DataTable dt = new DataTable();

dt = FillData_PaymentInfo();

SheetData sheetData1 = new SheetData(CreateSheetData_PaymentInfo(dt));

if (Dts.Variables["O_I_LS_IND"].Value.Equals("Y"))

{

DataTable dt2 = new DataTable();

dt2 = FillData_Loss();

if (dt2.Rows.Count >= 2)

{

SheetData sheetData2 = new SheetData(CreateSheetData_Loss(dt2));

var worksheetPart2 = workbookPart.AddNewPart<WorksheetPart>();

string relId2 = workbookPart.GetIdOfPart(worksheetPart2);

var sheet2 = new Sheet { Name = "Loss_Related", SheetId = 2, Id = relId2 };

sheets.Append(sheet2);

var worksheet2 = new Worksheet();

worksheet2.Append(sheetData2);

worksheetPart2.Worksheet = worksheet2;

worksheetPart2.Worksheet.Save();

}

}

if (Dts.Variables["O_I_CS_IND"].Value.Equals("Y"))

{

DataTable dt3 = new DataTable();

dt3 = FillData_Claim();

if (dt3.Rows.Count >= 2)

{

try{

SheetData sheetData3 = new SheetData(CreateSheetData_Claim(dt3));

var worksheetPart3 = workbookPart.AddNewPart<WorksheetPart>();

string relId3 = workbookPart.GetIdOfPart(worksheetPart3);

var sheet3 = new Sheet { Name = "Claim_Service", SheetId = 3, Id = relId3 };

sheets.Append(sheet3);

var worksheet3 = new Worksheet();

worksheet3.Append(sheetData3);

worksheetPart3.Worksheet = worksheet3;

worksheetPart3.Worksheet.Save();

}

catch

{

MessageBox.Show("claim");

}

}

}

if (Dts.Variables["O_I_LC_IND"].Value.Equals("Y"))

{

DataTable dt4 = new DataTable();

try

{

dt4 = FillData_CustomLoss();

}

catch

{

MessageBox.Show("cl");

}

if (dt4.Rows.Count >= 2)

{

try

{

SheetData sheetData4 = new SheetData(CreateSheetData_CustomLoss(dt4));

var worksheetPart4 = workbookPart.AddNewPart<WorksheetPart>();

string relId4 = workbookPart.GetIdOfPart(worksheetPart4);

var sheet4 = new Sheet { Name = Dts.Variables["O_I_Div_nm"].Value.ToString() + "(Loss)", SheetId = 4, Id = relId4 };

sheets.Append(sheet4);

var worksheet4 = new Worksheet();

worksheet4.Append(sheetData4);

worksheetPart4.Worksheet = worksheet4;

worksheetPart4.Worksheet.Save();

}

catch (Exception ex)

{

MessageBox.Show(ex.Message);

}

}

}

if (Dts.Variables["O_I_CC_IND"].Value.Equals("Y"))

{

DataTable dt5 = new DataTable();

try

{

dt5 = FillData_CustomClaim();

}

catch

{

MessageBox.Show("cc");

}

if (dt5.Rows.Count >= 2)

{

try{

SheetData sheetData4 = new SheetData(CreateSheetData_CustomClaim(dt5));

var worksheetPart5 = workbookPart.AddNewPart<WorksheetPart>();

string relId5 = workbookPart.GetIdOfPart(worksheetPart5);

var sheet5 = new Sheet { Name = Dts.Variables["O_I_Div_nm"].Value.ToString() + "(Serv)", SheetId = 5, Id = relId5 };

sheets.Append(sheet5);

var worksheet5 = new Worksheet();

worksheet5.Append(sheetData4);

worksheetPart5.Worksheet = worksheet5;

worksheetPart5.Worksheet.Save();

}

catch (Exception ex)

{

MessageBox.Show(ex.Message);

}

}

}

// Add the parts to the workbook and save

var workbook = new Workbook();

workbook.Append(fileVersion);

workbook.Append(sheets);

var worksheet = new Worksheet();

worksheet.Append(GenerateColumns_PaymentInfo());

worksheet.Append(sheetData1);

worksheetPart.Worksheet = worksheet;

worksheetPart.Worksheet.Save();

myWorkbook.WorkbookPart.Workbook = workbook;

myWorkbook.WorkbookPart.Workbook.Save();

myWorkbook.Close();

}

}

Answer 3

Tom,

With reference to my above post, i would like to explain what i'm doing with the code.

The Buildexcel () method will create an excel file (maximum 5 sheets - based on different conditions).

The method also invokes other User defined methods  - eg: FillData_CustomClaim(), which excutes a SQL query and returns a datatable of results.

CreateSheetData_PaymentInfo () , is another method that is invoked by BuildExcel(). This method will return an List<opemxmlElement>, which is basically the rows for the excel sheet. The rows are build out of the datatable returned by the FillData_..() methods.

Thanks

Binoy

Answer 4

Hi Binoy,

Please consider reposting all of the information above to the SQL Server Developer Center>SQL Server Forums>SQL Server Integration Services Forum where you'll be helped by experienced developers who are familiar with SSIS.

---

Please remember to mark the replies as answer if they help and unmark them if they provide no help. and click "Vote as Helpful" this and other helpful posts, so other users will see your thread as useful. Best Regards, Chris Jensen

Answer 5

Thank you Chris..