Spreadsheet is Full

Question

Im inserting data into Excel using SSIS.

Im getting the following exception:

'[Excel Destination [32]] Error: 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: "Spreadsheet is full.".'

I did some research using the following link:

http://office.microsoft.com/en-us/excel-help/excel-specifications-and-limits-HP010073849.aspx

It states that you can populate the maximum of 1,048,576 rows by 16,384 columns

The data i am importing into Excel is only about 100,000 rows by 5 columns

Why am I getting this exception?

FYI the following is my Data Flow Task:

ODBC Source to Data Conversion transformation to Excel Destination

Rosser

Its in SSIS I have an ODBC Source to Data Conversion transformation to Excel Destination...no insertion code – Rosser

Answer 1

The best work around for this was to use a Flat File Destination and then import the Flat File directly from Excel. This proved to perform a lot better that using SSIS to import to Excel which is extremely slow. This work around imported my data almost instantaneously rather than having to wait a half an hour.

Rosser

Answer 2

I sorted by changing the file to xlsx and changing the excel version (property excel connection manager) to Excel 2007-2010

Zackzackariah

Answer 3

I solved it by creating an empty EXCEL file with all the columns that should be filled during transformation, and created a table out of those columns in the Excel file. So you can do so by selecting the columns and click on "Format as table" option available to you. Once the Excel file is ready, you can save it as xlsx extension and try loading that excel file again. Now you will be able to load the excel file with higher number of rows in it.

Drawbar

Answer 4

My solution:

• Installed Microsoft Access Database Engine 2016 Redistributable from here.

• Opened SQL Server 2019 Import and Export Data (64-bit) from Windows startup.

• Chose Microsoft OLE DB Provider for SQL Server as data source.

• Chose Microsoft Excel as destination.

• Chose a path for the Excel file with the .xlsx extension (the file was not created beforehand).

And it worked so fast. That's it.

Er