CODEWITHSUNDEEP
sql-serverdtsssis
Julio Guerra
Julio Guerra

Reputation: 5661

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.

Upvotes: 0

Views: 3020

Answers (2)

Julio Guerra
Julio Guerra

Reputation: 5661

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.

Upvotes: 1

Gennady Vanin Геннадий Ванин
Gennady Vanin Геннадий Ванин

Reputation: 10384

I would not use SSIS for this, you may have Excel2007 as linked server , putting data into Excel by regular TSQL, or process data by Excel VBA getting data directly from SQL Server. As a matter of practical sanity, I would not ever use SSIS for anything

Well, there is not much information how you do it but you should specify somehow that first row should not be used as header names container (HDR=NO), something like,

  • insert into OPENROWSET('Microsoft.Jet.OLEDB.4.0',
    'Excel 8.0;Database=D:\testing.xls; ; HDR=NO',
    'SELECT * FROM [Sheet1$]')

Upvotes: 1

Related Questions