SSIS Custom Task for creating Excel with formulae

  • Hello everyone,

    I am currently looking for a way to create Excel reports that include formulae (probably also Pivot tables and other stuff) from Reporting Services. Searching the web and the forums on SSC I found that therer is no simple way to achieve this.

    Would it be possible to do such a creation in an SSIS Script or Custom Task? If yes, is it also sensible to do this or are there too many limitations or caveats to make this work?

    Cheers

    Markus

  • As far as I know, SSIS is going to be complicated to do this.  You are basically going to need to build up a custom script in C# (or equivalent) to run in SSIS.

    What is likely going to be the easiest way to do what you need (I am making a LOT of assumptions here) is have one workbook in Excel that connects to your SQL data source to pull in the raw data.  Then use other workbooks to pull data from the Raw data via pivot tables for example.

    Excel can either pull data directly from SQL or from another data source (such as SSRS).  There are advantages and disadvantages to each approach, so pick one that works best for you.

    Disadvantage to the "do it all in Excel" approach is end users need to click the "refresh all" button to make it refresh.  You can have it auto-refresh on file load, but I find this sometimes causes issues so I do not recommend turning that on.

  • msdeibel wrote:

    Hello everyone,

    I am currently looking for a way to create Excel reports that include formulae (probably also Pivot tables and other stuff) from Reporting Services. Searching the web and the forums on SSC I found that therer is no simple way to achieve this.

    Would it be possible to do such a creation in an SSIS Script or Custom Task? If yes, is it also sensible to do this or are there too many limitations or caveats to make this work?

    Cheers

    Markus

    The simplest (!) way of doing anything sophisticated with Excel is to install the Excel client program on the server which is doing the work. Once you have this in place, you can programmatically create an instance of Excel and do almost anything a user would be able to do.

    But if you do not like the idea of installing Excel on a server, you are not alone.

    If the answer to your question can be found with a brief Google search, please perform the search yourself, rather than expecting one of the SSC members to do it for you.
    See https://www.sqlservercentral.com/articles/forum-etiquette-how-to-post-datacode-on-a-forum-to-get-the-best-help/ for details of how to post T-SQL code-related questions.

  • This would be extremely laborious to do in SSIS, and I wouldn't recommend it. I would use either Power BI and its "Analyze in Excel" feature for something like this, or an SSAS cube and Excel pivot table workbook connected to it.

  • Phil Parkin wrote:

    msdeibel wrote:

    Hello everyone,

    I am currently looking for a way to create Excel reports that include formulae (probably also Pivot tables and other stuff) from Reporting Services. Searching the web and the forums on SSC I found that therer is no simple way to achieve this.

    Would it be possible to do such a creation in an SSIS Script or Custom Task? If yes, is it also sensible to do this or are there too many limitations or caveats to make this work?

    Cheers

    Markus

    The simplest (!) way of doing anything sophisticated with Excel is to install the Excel client program on the server which is doing the work. Once you have this in place, you can programmatically create an instance of Excel and do almost anything a user would be able to do.

    But if you do not like the idea of installing Excel on a server, you are not alone.

    not supported by Microsoft and against licensing rules to install a Client only application on a server.

    And would require anyone with potential access to that server (directly or indirectly) to be licensed for the same version of Excel.

  • you can do it - but not easy.

    option 1 - Sharepoint Excel Services - https://docs.microsoft.com/en-us/sharepoint/dev/general-development/excel-services-overview 

    option 2 - use OpenXML https://github.com/OfficeDev/Open-XML-SDK - C# code - better as an independent app but could be done as a SSIS C# Script

  • not supported by Microsoft and against licensing rules to install a Client only application on a server.

    I found the following text here:

    Besides the technical problems, you must also consider licensing issues. Current licensing guidelines prevent Office applications from being used on a server to service client requests, unless those clients themselves have licensed copies of Office. Using server-side Automation to provide Office functionality to unlicensed workstations is not covered by the End User License Agreement (EULA).

    My reading of this is that as long as the Excel installation on the server is licensed along with (of course) all clients which are going to use the results of any Excel automation, there is no problem from a licensing point of view.

    I agree that it's unsupported. But such automation is in place and running successfully at (I would guess) thousands of companies. It's perfectly doable.

    If the answer to your question can be found with a brief Google search, please perform the search yourself, rather than expecting one of the SSC members to do it for you.
    See https://www.sqlservercentral.com/articles/forum-etiquette-how-to-post-datacode-on-a-forum-to-get-the-best-help/ for details of how to post T-SQL code-related questions.

  • Thanks for all the answers so far. This is a lot more promising than what I read until now.

    The most likely approach for me, would now be a combination of a C# implementation and one of

    1. Excel installation on the server plus Office.Interop libraries
    2. 3rd party lib

    3rd party libs could be the one proposed by @frederico_fonseca or spreadsheet light with which I've worked before.

    The only commercial product that came close was the one from DevExpress, but also their server can't do these things out of the box.

Viewing 8 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply