To build SSIS packages (which will export to Excel then email the report), I need Visual Studio 2019. (The backend database instance is SQL 2019)
Lots of choices for which components to download:
Do I need anything other than Visual Studio Professional 2019?
Why not create it as a SSRS report and give users access? Hasn't this post answered your question?
@yosiasz ,I know we have touched on this subject before. But don't I need VS to develop an RS report? The report needs a lot of flexibility. For example, sometimes it will run for All divisions, sometimes for only one division. Then the (excel) report will need to be emailed to recipients whose addresses exist in a table.
Does RS have the ability to do these requirements?
What determines/drives wether it is for all divisions or one?
You dont need vs to write rs (also already covered this with you, look back in threads)
Ssrs has flexibility of doing what you mentioned
Yes - SSRS has that capability. You can setup subscriptions and schedule those to run with pre-defined parameters (e.g. all divisions, individual users, etc.).
If you install Enterprise Edition - you would also have the ability to use data-driven subscriptions. These would generate the reports based on a query/stored procedure that defines the parameters, the to/cc/bcc users, etc...
Honestly, for something like this SSRS is going to provide a much more flexible solution than using SSIS to generate Excel files. Not only can you provide those files, but you can also provide access directly to the reports - create snapshots to keep multiple copies - create linked reports with fixed parameters (e.g. an All Division report that passes the parameter and is hidden from the user).
And instead of sending the Excel spreadsheets - the users can access SSRS and run the report as needed with whatever parameters they need at that time. Then they can export in whatever format works for them...
One final note: I have often ended up building 2 reports using the same stored procedure as the source, with the same parameters. One of the reports is built as a normal paginated report - with page headers/footers - fully structured for display. The second report is a simple matrix - nothing else - that can then be exported to Excel so that the exported file ends up as a single header row and detail rows, which is easily modified by the users to freeze the column header and add filtering.
And save paper and my Inbox 
The minute an excel file is disseminated it is already stale
@jeffw8713 Thanks for your detailed reply.
@yosiasz These excel reports will be distributed to external customers via email. They will not have access to our RS Dashboards.
... We have defined the (email) recipients in a table. based on which Division the report is being ran for, we retrieve the corresponding email addys for that division or for *ALL divisions. My stored proc handles this well but it only generates an HTML report. Thus the need for excel. From what I've read, generating a pretty Excel report from TSQL is very tricky. If that's not the case, please enlighten me. Thanks
In SSRS you would use a data-driven subscription and the code that you have now to determine the email addresses to be used. You can simplify the process because now you have a simple stored procedure that returns the data - and a separate procedure or code that just returns the email addresses.
It also sounds like you want a formatted Excel spreadsheet - which is going to be an issue in either T-SQL or SSIS. In either method, you have to have a 'template' Excel file already formatted where the process can then just add the data. If there is any significant formatting to be done - this becomes much more difficult.
From SSRS the exported Excel report is nicely formatted the same as the report and there is no requirement or need to use a template. Much, much, much easier to manage and maintain.
If you don't have Enterprise Edition for SSRS - then investing in 2 2-packs (for a 4 vCPU system) is well worth the money spent.
Good advice, @jeffw8713 But is a 4 processor VM a substitute for the Enterprise Edition? Please clarify. Thank you.
No, Enterprise Edition is licensed by CPUs so you would only need to.purchase for that server
A .PDF is not useful for our needs. Must be Excel that is dictated by our clients.
Sorry, but I'm still not clear as to why SQL Enterprise is required. Thanks
Data driven subscriptions are only available in enterprise
So I need to install:
SQL Server 2019 Enterprise
Reporting Services 2019
Report Builder 2019
And Visual Studio is not required.
Correct?
Thanks
Correct.
When you install ReportingServices, it prompts for the Product Key. In my case (intially), I will use SQL Server Developer edition. When I install Reporting Services, can I just enter the product key for SQl Server Developer? (I don't think there is a Product Key associated with the free Dev Edition or am I wrong?)
I would install Visual Studio and add the data tools extensions. This will give you the tools to develop both SSIS and SSRS solutions - and provides more flexibility when designing reports that report builder (at least it did the last time I checked).
If you are installing everything on your workstation to test and validate, then yes - you need SQL Server Database Engine (Developer Edition), Reporting Services (Developer Edition) and either Report Builder or Visual Studio (or both).
For Visual Studio - you can install the free Community Edition and just add the extensions for SSRS and SSIS. If you have an MSDN subscription and licenses for Visual Studio you can install that version - but you don't need to purchase anything just to develop SSIS/SSRS solutions.
See this article: Install SQL Server Reporting Services - SQL Server Reporting Services (SSRS) | Microsoft Docs
It clearly shows that you will have an option for Developer Edition when installing.
If you are building the server(s) - then you would install the licensed versions of the product on each server. For the SSRS server you would install Enterprise Edition for both database engine and reporting services, and would only need VS and/or Report Builder on your workstation.
That's very helpful information, @jeffw8713. Before I go any further down this path I need to say that by "data-driven" subscriptions in our case that means we retrieve the email addresses dynamically each time the report runs. We're not looking for the traditional RS Dashboard where reports are there to be clicked on at anytime. Of course we will use those eventually but our immediate need is to run report jobs and email the report to external customers.
@jeffw8713 Another question. Our Production database instance is 2019 Standard edition. If I build a reporting server that's 2019 Enterprise, will the IS and RS jobs encounter any issues in reading from the Standard instance? Thanks
