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

Posted on 2013-10-17
Medium Priority
Last Modified: 2016-02-11
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?

Question by:jeffrey_b_lee
LVL 44

Accepted Solution

Rainer Jeschor earned 1200 total points
ID: 39582399
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)

LVL 16

Assisted Solution

DcpKing earned 800 total points
ID: 39582487
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!



Author Comment

ID: 39588827
Looking into these solutions.

Featured Post

Build your data science skills into a career

Are you ready to take your data science career to the next step, or break into data science? With Springboard’s Data Science Career Track, you’ll master data science topics, have personalized career guidance, weekly calls with a data science expert, and a job guarantee.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Microsoft Access has a limit of 255 columns in a single table; SQL Server allows tables with over 255 columns, but reading that data is not necessarily simple.  The final solution for this task involved creating a custom text parser and then reading…
Microsoft provides a rich set of technologies for High Availability and Disaster Recovery solutions.
This Micro Tutorial will demonstrate the scrolling table in Microsoft Excel using the INDEX function.
SQL Database Recovery Software repairs the MDF & NDF Files, corrupted due to hardware related issues or software related errors. Provides preview of recovered database objects and allows saving in either MSSQL, CSV, HTML or XLS format. Ensures recov…

607 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question