Did you ever faced a situation when needing to generate dynamically a SQL Server Reporting Services Report on-demand, at some strange hour, such as 4:30 AM because of the different timezones involved or because it takes so much time and the C(E/I/T/X/S)O needs to get the information right before 7:15 when arriving to the working place?
If you are a proud & empowered user of SQL Server Enterprise Edition with an Enterprise Edition of SharePoint – there are a couple of things that you can do, such as Subscriptions.
Well, if you did not know, since 2009 there is a great free and open-sourced alternative – SSIS ReportGenerator Task, developed by the non-the-less author of SSIS Google Analytics Source, my friend and colleague from OH22 Tillmann Eitelberg.
Supporting SQL Server versions 2008/2008R2/2012/2014 (the latest release is compatible with 2012 & 2014), this little Integration Services Task allows you to render your Reports in a number of different formats, such as XML, CSV, PDF, HTM (HTML4), HTM (WebArchive), XLS, TIFF and DOC.
Local & Remote
With SSIS ReportGenerator Task you are not limited to generate reports locally on the server, you can configure it to run on the remote Reporting Services installation, or even use a SharePoint 2010 (It might work on 2013, but I have not heard of anyone doing this so far).
But that above statement was quite obvious, wasn’t it ?
Well than what about the local recordset Reports being rendered ? The 1.8 version of the ReportGenerator supports this requested functionality. This is what is being identified as a local rendering.
Parameters & Parametrization
If your report has some input parameters, it will be enough to select your Report and to assign it to the “Report” Attribute at the Properties tab and after a couple of seconds all the external parameters shall be recognised and you can start using your SSIS packages variables to configure the generated values dynamically, with the logic that you need.
Be careful that the changing the selected Report might make your old parameter selection obsolete and might even lead you to restart task configuration.
What’s new in version 1.8
The latest version released less than a month ago (August 2014), has introduced a number of requested and needed functionalities.
Besides a number of squashed bugs, there is a support for the latest & greatest SQL Server version as of the moment – SQL Server 2014,
support for the multi-parameter (string values separated by a common separator – 1,3,5,7,11 for example),
advanced properties for better control of the WebService Timeout (In complex reports this might make a huge difference to you).
Check out SSIS ReportGenerator Task now!
