How to add new columns to an existing Excel spreadsheet using SSIS

• jausubel

  SSC Enthusiast

  Points: 165

  More actions

  August 28, 2015 at 2:16 pm

  #307229

  Using this data provider & options: Microsoft.ACE.OLEDB.12.0;Extended Properties="EXCEL 12.0 XML;HDR=YES

  I need to create an Excel file with dynamic column definitions and populate it.

  The Excel table will have a large number of columns, and the columns definitions will vary, depending on an input parameter value.

  So far, I've managed to generate an Excel file, and then create a table (sheet) using the following syntax:

  CREATE TABLE `Report` (

  Col1 integer,

  Col2 varchar(20),

  Col3 decimal(7,2)

  )

  My issue is that I'm able to create an Excel table limited to 256 columns, using this approach.

  Beyond that, I get a "Too many fields defined" error.

  I've tried splitting the DDL, executing a CREATE TABLE task, followed by a subsequent task using an ALTER TABLE command:

  ALTER TABLE `Report` ADD Col4 decimal(15,4)

  I don't know whether or not this is a syntax issue. I haven't found any examples online.

  The ALTER command doesn't result in an error, but it hangs.

  Anybody have any suggestions?