6

I'm having an issue processing an excel file with SSIS unless I manually open the file and click 'save'.
I've noticed that it doesn't mater if the file is .xls or .xlsx

The file is downloaded to excel from an SSRS project
I am able use a File System Task to move, rename, delete, etc. the file, but when I try to use a dataflow task to access the contents inside the file I get an error that the external table is not in expected format.
I then have to open up the file, click save, and the file processes fine.

Not sure if this means anything but....
I've noticed that when I open the original file in notepad the top line includes with:

xl/workbook.xml

After I save the file the top line changes to:

[Content_Types].xml 

Using Microsoft Visual Studio 2012.

ERROR:

Error: 0xC0202009 at TransferMoneyReconcile, Connection manager "Excel Connection Manager": SSIS Error Code DTS_E_OLEDBERROR. An OLE DB error has occurred. Error code: 0x80004005. An OLE DB record is available. Source: "Microsoft Office Access Database Engine" Hresult: 0x80004005 Description: "External table is not in the expected format.".

ExcelConnectionString:

Provider=Microsoft.ACE.OLEDB.12.0;Data Source=\192.168.234.567\ftp\Sample\Money\Archive_Transfer Money to Manager.xlsx;Extended Properties="EXCEL 12.0 XML;HDR=NO";

any help/suggestions are appreciated!
Thanks!
Steven

Doolius
  • 854
  • 6
  • 18
  • I'm curious - why export to excel and then use SSIS to get back to move data from it? Why not use the original data source the report uses? – april4181 Dec 11 '14 at 18:35
  • Also, can you post the exact error you're getting? – april4181 Dec 11 '14 at 18:38
  • Our customers download the reports with certain input parameters (that update certain columns) and place them on an FTP for us to pickup and process which in turn triggers other updates. Sometimes they have to go in and edit something and the file processes fine because they edit/save it, but other times everything is good but they still have to open the file and save it for us to be able to process. Error added to original post. – Doolius Dec 11 '14 at 19:04
  • I was thinking about having a script task try and open the file and re-save it, but I haven't been able to find a good enough example to try out. – Doolius Dec 11 '14 at 19:05
  • Try setting the delay validation property of your data flow task to true. I've had some weird errors with SSIS and Excel that that has fixed for me. – april4181 Dec 11 '14 at 19:43
  • Delay validation is set to true. – Doolius Dec 11 '14 at 20:57
  • I was able to recreate your error! I posted the solution that worked for me below. – april4181 Dec 12 '14 at 16:13
  • Let us [continue this discussion in chat](http://chat.stackoverflow.com/rooms/66779/discussion-between-doolius-and-april4181). – Doolius Dec 12 '14 at 19:34
  • https://stackoverflow.com/questions/27427536/cant-open-excel-file-with-ssis-unless-it-is-manually-saved/68223598#68223598 – Stuart Steedman Jul 02 '21 at 10:29

2 Answers2

0

XLSX extension is of 2 types.

  1. Excel workbook (let's call this type 1)

  2. Strict Open XML Spreadsheet (let's call this type 2)

Both the above have the .xlsx extension.

To see this yourself, you can open the excel application, create a new file, click on save and notice the type 1 (located at top of the list) and type 2 (located at the bottom of the list).

The default option is type 1 - Excel workbook. So when you create a new file and save, it will get saved as type 1.

Based on my testing, however, when you open a type 2 file and save, then it may get saved as type 1 or type 2. This behaviour is not guaranteed.

Anyways, to resolve the error you are facing, open the file and save it (via save as) and choose type 1 format. Then test the SSIS, it should run without the error.

variable
  • 8,262
  • 9
  • 95
  • 215
0

This might throw more light on it. The questions remain:

  • Why can't SSRS output in the standard Excel Workbook format?
  • Why can't SSIS load the Strict Open XML Spreadsheet format?

Before

After

Stuart Steedman
  • 257
  • 2
  • 6