Am trying to query a table with 260 columns and write to an Excel output.
In Excel Destination -> Name of the Excel Sheet -> I clicked on NEW and tried to take the default data table.
However, I get a Microsoft JET Engine: Too many fields error message.
Writing to an existing file is giving me a 64-bit error message. I have the "Run 64bit Runtime" set to False in Project Properties.
Is there any way I can make this Export to Excel work?
jet 4.0 engine writes to .xls files. these files have a limit of 256 columns iirc.
if you want to write to .xlsx files, use/install the microsoft ace 12.0 engine. there is 32-bit and 64-bit versions. but beware: match the "bitness" of your OS and installed Office.
a more programmatic way would be to use the epplus or npoi .net libraries, which read/write directly with the Excel file formats. the coding is much like you would do if using the Excel COM objects.
I've done this by having my SQL-Excel data transfer code in Poershell scripts, using Epplus.dll. then Ssis invokes the powershell scrips.
