xlsx error: "Removed Records: Named range from /xl/workbook.xml part" when tried to resolve errors

Question

I have an xlsx file that I generate using SSIS. The data to this file is written through a data flow task where the xlsx file is the oledb destination. I have used the following connection string as an expression for the OLEDB connection:

"Provider=Microsoft.ACE.OLEDB.12.0;Data Source="+ @[User::ExcelPath] +";
Extended Properties=\"Excel 12.0 Xml;HDR=YES\";"

The file opens just fine before the data flow task. After the data flow task, I get a dialog as follows:

When I click yes, I get another message:

The contents of the xml file are

<?xml version="1.0" encoding="UTF-8" standalone="yes"?>
<recoveryLog xmlns="http://schemas.openxmlformats.org/spreadsheetml/2006/main">       <logFileName>error072840_02.xml</logFileName><summary>Errors were detected in file   'C:\output\documentId-1.xlsx'</summary><removedRecords summary="Following is a list of removed records:"><removedRecord>Removed Records: Named range from /xl/workbook.xml part (Workbook)</removedRecord></removedRecords></recoveryLog>

I am not able to figure out what exactly is causing this error. Any help will be appreciated. TIA

Answer 1

I had this issue when I was using EPPlus to customise an existing template. For me the issue was in the template itself as it contained invalid references to lookup tables. I found this in Formula -> Name Manager.

I suggest that you check the template if you face this issue.