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.