Using SSIS and Importing Data Into An Excel 2010 Template with Totals

We have an excel template that is going out to clients.  We'd like to use SSIS to export data into the template (that has headers) and still preserves the totals row which is at the bottom of the report (after the last row).  

For example, if a client has one record and we import that one record into the template, the totals field will still be there and isn't "pushed off" the page.

But if another client has 80 records and we try to import those rows into the Excel template, the totals will be pushed off the first page.

Is there away for us to do this without having any code inside the Excel template?

Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Rainer JeschorCommented:
which SQL Server version/edition?
From my experience there is no easy solution for this.
A couple of ways I can think of:
1. Create the Excel file / fill the template and after the data flow use a script component / Excel COM objects to add the total row to the Excel file
Con: COM used - possible memory leaks, Excel has to be installed on that machine where SSIS runs

2. Create / adjust the Excel file using the Office Open XML SDK
Pro: pure managed code, no local Excel installation necessary
Con: a bit more complicated in the code

3. Create the data in the Excel workbook in a dedicated sheet and use a Pivot table on another sheet to display the data (including the total row)


Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
One other possibility that Rainer may have missed: if the totals row just has to be a set of the correct totaled numbers (as opposed to real totals calculated within the spreadsheet) then you can always create the table as a temp table or tablevar and append one extra row to the end - each field being the sum of all the other fields in that column!


jeffrey_b_leeAuthor Commented:
Looking into these solutions.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.