SQLTeam.com | Weblogs | Forums

How to schedule and send SSISDB/Standard Reports/All Executions



I need to build a SQL Server Agent job that will run the SSMS built-in report found under SSISDB/Standard Reports/All Executions and send it to a list of recipients.

I do not want to have to run the report interactively from SSMS, but have the report run on a scheduled basis so that it is ready and distributed to the required recipients before the start of their business day.

How do I do that?


I think you'll need to run a trace or an XE session to figure out what queries those SSMS reports are using.


Yup, thought of that, but was hoping for a simpler solution. Don't want to recreate the built-in report(s), just run them on schedule.


Couple of years back, I had to do something like this. I created a stored proc that could be called to run the report and mail it. First, you set up a subscription for the report on the report server and then use the stored proc with the report path.

This was not perfect; it has at least two problems/issues: a) if I had two subscriptions on a report, this would have failed, or picked up a random one (not sure which). b) this does not give you control over who to mail it out to etc. That is pre-defined in the subscription. But, for my purposes, this was good enough.

If you develop something more robust/flexible, and if you can share it, that would be appreciated.

CREATE  PROCEDURE [dbo].[GenerateReport]
	@ReportPath VARCHAR(255)
-- To generate a report based on a subscription if the path of the report is given.
-- This is the report to be generated.
-- example: @ReportPath '/MyReports/AverageCost'

SELECT  @subscriptionId = rs.SubscriptionId
FROM    ReportServer.dbo.ReportSchedule RS
        INNER JOIN ReportServer.dbo.Catalog C ON ( C.ItemID = RS.ReportID )
WHERE   Path = @ReportPath ; 

IF (@subscriptionId IS NULL)
	INSERT INTO dbo.DIANGOSE_ReportRuns VALUES (GETDATE(), @ReportPath,@subscriptionId, 0);
	SET @msg = 'No scheduled report was found for path = ' + @ReportPath;

exec [ReportServer].dbo.AddEvent 
INSERT INTO dbo.DIANGOSE_ReportRuns VALUES (GETDATE(), @ReportPath,@subscriptionId, 1);


The dbo.DIANGOSE_ReportRuns is my debugging/logging table.


Thanks James. I have no problem scheduling regular reports (that I develop). It's the standard reports built-in to SSMS that I'm having trouble with. There are no RDLs (that I can find so far). The reports do not appear on the report server at all.

I want to use the standard reports but run those on a schedule.

Don't yet know if it's even possible.


Got an answer from msft; You can't do this.

we need to re-create the reports in Reporting Service.
Based on my understanding, the SQL Server built-in Reports are generated by SSMS itself. It is not available to be controlled by SQL Server Database. So we cannot use T-SQL to export it out as a rdl report or create a Agent Job for it.


Yeah that's what I figured. Run a trace or XE session to get the queries and then just put those in an email report via Database Mail.