Data-Driven subscriptions: Data-Driven Subscriptions - SQL Server Reporting Services (SSRS) | Microsoft Docs
Think of your SSRS instance as a standalone reporting system. Reports deployed to SSRS can connect to any ODBC data source. This includes Oracle, MySql, SQL Server (any edition and version), PostgreSQL, Intersystems Cache, Vertica and others.
As long as you can create a connection and execute some flavor of SQL code then the reports can be built and deployed to SSRS.
By separating the SSRS instance from your production system, you also separate the Edition and version requirements. You can upgrade your SSRS instance to SQL Server 2022 - leaving your application on SQL Server 2019. Or you can upgrade your application system to 2022 - leaving SSRS on 2019.
I would also recommend you take a look at the Power BI Report Server: Power BI Report Server | Microsoft Power BI
1 Like
@jeffw8713 I'm searching for VS 2019 Community. I prefer to not use 2022 as it likely has more advanced options that I need.
Visual Studio Older Downloads - 2019, 2017, 2015 & Previous Versions Under 'Still want an older version'? 2019 doesn't have an option for the Community Edition. Do you happen to know of another location to download it from?
"The minute an excel file is disseminated it is already stale"
100 percent true. However this is the requirement. These excel reports must be emailed to people who are outside the company. They will not have access the our internal report services dashboard of reports.
With that said, is SSRS still the best way to go?
I would still recommend using SSRS to generate the Excel reports - using the data-driven subscription option that is available. It will be much easier to design and format the Excel spreadsheet than trying to do that in SSIS.
Again, for SSIS you would need a template Excel spreadsheet. You then copy that template to a new file, populate the data in that spreadsheet, then email a copy of that file to the list of recipients. Then you need to either delete that file or archive it - or build the process to overwrite the next time it runs.
Then you have to deploy that project to the catalog, and setup individual jobs for each group - or you have to build all of that logic into the SSIS package so each time it runs it pulls the right data, loops over that data creating a file, sending the file and looping again.
That is a lot of work...
On the SSRS side - you create a report with appropriate parameters and setup a data-driven subscription. That subscription pulls the list of recipients and any specific parameters - generates the report and sends it to those recipients. Much less work - in my opinion.
1 Like
Thanks @jeffw8713. Now that we have determined which tool is best - Report Builder/SSRS - I'm building my first simple report. I'll start another thread with issues as they come up.
@jeffw8713 or @robert_volk One more point before I close this thread. You have stated I need the Enterprise Edition of 2019 for my Reports server running RS and IS. I can do that. However, our database (the source for these new reports) is the Standard Edition and it will remain so.
Will I be able to use data-driven subscriptions if my backend is the Standard edition? Thanks again.
The data source for a report can be any edition of SQL Server, or any supported database engine for that matter. Anything that can connect via OLEDB, ODBC or other supported driver.
1 Like
Thanks for clarifying that, @robert_volk. A lot is riding on this one point so I had to be sure.