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

SQL Server Administration
1

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?

2

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

3

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.

4

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)
AS
-- 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'

DECLARE @subscriptionId UNIQUEIDENTIFIER;
DECLARE @msg VARCHAR(255);
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)
BEGIN
	INSERT INTO dbo.DIANGOSE_ReportRuns VALUES (GETDATE(), @ReportPath,@subscriptionId, 0);
	SET @msg = 'No scheduled report was found for path = ' + @ReportPath;
	RAISERROR(@msg,16,1);
	RETURN;
END

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



GO

The dbo.DIANGOSE_ReportRuns is my debugging/logging table.

5

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.

6

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.

7

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.