Search code examples
sql-serverdtsssis

How to export data to an Excel 2007 table using SSIS?

I have an excel file (xlsx) containing a table :

Excel Table

Once I launched my ssis task (successfully) to insert data in it, it is actually append after the table :

Excel Table after the SSIS task

My expected result:

Expected

So I am looking for a way to insert into the table and expand it with the data. I hope someone could help me.

Solution

  • I finally found an answer. So I needed to generate excel reports with a lot of pivot charts linked to a main table.

    But using a table was a bad idea. Instead, the pivot charts must be linked to a named range.

    The last thing to know is that the error message "Invalid References" appears if the named range doesn't use the OFFSET function.

    My named range formula is :

    =OFFSET(Sheet!$A$1, 0, 0, COUNTA(Sheet!$A:$A), NUMBER_OF_COLUMNS)

    Where Sheet is the name of the worksheet and NUMBER_OF_COLUMNS is the number of columns of the data.

    That's it. I can now generate excel report without any line of code, only using SSIS 2005.