January 11, 2012 at 2:49 pm
Hi all,
Got a problem that I've been wrestling with for a while now. We have some scripts which produce Excel reports, that were coded using old-fashioned techniques such as MS Access, VB Script, etc...
Part of my project is to convert these in to new, easier to use ones. So I'm using SSIS.
SSIS exporting data into Excel is fine for the most part, except that SSIS doesn't really provide you with a whole lot of control over what goes in to the Excel file. You can write rows in, and you can set the format of the rows by providing a template row, but that's pretty much it.
My problem is that I need to do quite a bit more than just that. As a simple example, I need to have a row at the end of all my data rows, which provides a summary of all the rows above it. I could technically insert this row by reading the row from the database, but it's kinda clunky. Also, for formatting, I'm reliant upon having the formatted row be a template row so that the rest follows it, but that makes things tricky if I want to change the formatting.
The solution which I've used up 'till now is to use Excel automation - using a Script component, I manipulate the Excel file to set the formats that I want.
Problem is that the server which I'm running these scripts on doesn't have MS Office installed, and without MS Office installed, you can't access the Interop.Excel libraries.
So far I have a few options to test:
1) Install Excel Viewer. I've seen some people say this may allow access to the Interop.Excel libraries, as the viewer may install the stuff to the GAC.
2) Switch to using OpenXML. This is going to require me to do a considerable amount of research to figure out how to do it, so I'd rather not need to take this option unless I have to.
3) ???
Anyone have any other suggestions?
January 11, 2012 at 2:57 pm
In most cases for formatting I'll use a template file instead of a template row. I'll setup/format the file and then copy it using filesystem options as my target file, and work with that instead.
For the final row you're kind of out of luck. Either you append a totalling row or you have to use Excel automation to include the summing formulas. I don't believe you can activate a macro built into the copied worksheet without having the necessary VBA drivers.
Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.
For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA
January 11, 2012 at 3:03 pm
Yeah - not to mention other things like, in some cases I need to have a single worksheet contain multiple result sets within it - which means that I have a header - content - summary block multiple times, something that a template wouldn't support.
I'm thinking that there really is no other effective alternative than using Excel automation ... guess I'm going to have to try to convince them that it's the only solution :/
January 11, 2012 at 3:23 pm
If your data source is SSIS, you might give users spreadsheets that fill in data by PowerPivot, and grant them access to their cubes.
There is a drawback, though: once users have workbooks on their machines, it is rather difficult to upgrade them to a newer version.
Handling of Excel with an SSIS backend is one of the selling points of SharePoint. (Yes, I know.)
January 11, 2012 at 3:25 pm
I'm guessing PowerPivot is something which is not in SSIS 2005?
January 11, 2012 at 4:52 pm
It is a free add-on to Excel.
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply