October 11, 2015 at 9:11 pm
Hi,
Currently, I have a report on SSRS ( SQLServer 2008R2) and accept a invoice number as a parameter. The report will generate the information related to this invoice in 1 report. So, 1 invoice 1 report. If I need to generate 2 invoices, I need to run twice.
I would like to schedule it and generate reports automatically once the invoice appears in the database and save the output as PDF for each in a unique file name.
Just wonder what's the best option for me to do it
1) Use SSIS. Create a For..Loop to execute a SQL statement to return all invoice number that fulfills the criteria and then call SSRS report.
2) In SSRS, there's a subscribe option but I can't find any way I can pass in the parameter automatically. Or some configuration that I missed out ?
Hope you can provide me some ideas. Thanks.
October 12, 2015 at 7:33 am
If you are using Enterprise Edition then Data Driven Subscriptions would be the way forward here.
You define a script that it will run periodically and use any of the values returned by the script as parameters for the report (i.e. the invoice numbers) and also the subscription (e.g. output file name, email destination, export format etc).
October 13, 2015 at 3:37 pm
Unfortunately, I am running on Standard Edition so Data Driven Subscription is not an option anymore.
Have you try using SSIS as an alternative ? I am just unsure the process on how to do it in SSIS.
October 15, 2015 at 7:17 am
Hi David,
I've been spoilt by Enterprise Edition for a while now but I have used SSIS for this before and it requires a custom task.
Try https://reportgeneratortask.codeplex.com/.
There's a few about, but this seems good and Tillmann's Google Analytics custom source was also excellent.
Your method is fairly sound. Use an Execute SQL Task to return your parameter values as a result set into a variable then use ForEach to iterate over the values passing them into the ReportGenerator task.
There's a decent write up of iterating over a resultset here: https://coldlogics.wordpress.com/2011/04/09/using-ssis-to-dynamically-create-data-files-from-a-full-result-set/. You can pass the parameter to a ReportGen task instead.
Set it up as a SQL agent job to run periodically and you're all set.
N.b. The task needs to be installed wherever the package is being run from, so if you plan to deploy the package to a server you'll need to be able to install it there as well.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply