Hi,

I have ssis package where I have excel connection manager with expression pointing to a variable which has path for location and name of excel spreadsheet to be create each with date on the name.

ExcelFilePath points to variable for shared location where excel file will be saved.

I have File system task for copying template excel file to destination location with date in file name.

I drag and drop excel destination.  Pointed to excel connection manager. Under data access mode, I have select table and view.  

When I try to select name of excel sheet,  it says, no tables or views could be loaded. I should be able to see sheetname there so that I can map column. I only have option to create new spreadsheet. I want to use template to load data in excel file. I dont want to create new sheet. 

Am I missing anything here. It was working before. But I opened the ssis package and its broken. I was able to see spreadsheet name before but I dont see it now even though I have not made any change to package. I am not able to figure it out what happen suddenly.

Thanks for help.

This is what I see in connection string:

Provider=MicrAosoft.ACE.OLEDB.12.0;Data Source=\\Dev\Test\Destination\ABC -2015-09-13.xlsx;Extended Properties="EXCEL 12.0 XML;HDR=NO";

• Edited by 1234alex 9 hours 15 minutes ago

Hi 1234alex,

\\Dev\Test\Destination\ABC is not accessible to the account executing the package.

Typically resolved by using a proxy: https://gqbi.wordpress.com/2014/01/30/setting-up-a-proxy-account-to-run-sql-server-integration-services-ssis-2012-packages/

Hi 1234alex,

Based on my research, the issue can be caused by the followings:

• All the sheets in excel file are hidden, in this case, the drop down does not allow you to select values from a hidden sheet. To fix this issue, we can select "Table name or view name from variable" or "SQL Command." as Data access mode in Excel Destination. The following similar thread is for your reference:
  http://stackoverflow.com/questions/8036391/ssis-hidden-sheets-as-excel-destination
• The excel file is corrupted. We can open the excel file to check the issue. The solution is repair the corrupted workbook manually. For more details, please see:
  https://support.office.com/en-za/article/Repairing-a-corrupted-workbook-7abfc44d-e9bf-4896-8899-bd10ef4d61ab

Thanks,
Katherine Xiong