List all the SSRS Report Subscription and Job linked with subscriptions.

SSRS Reports provide automation using Subscriptions. Specially Data Driven subscriptions provide more flexibility in automation.

As soon as a SSRS Report Subscription is created a SQL Agent Job is created in SSRS Report Database Server, which execute on schedule and make sure subscriptions are executed. Job names are UNIQUE IDENTIFIERs and not so friendly. To associate SSRS Report to Subscriptions to SQL Agent Job, following query can be used. And later we can update Job like enable/disable from backend OR get SQL query for subscription execution and use it in ad-hoc reequipment and do many more backend stuff.

Use the query and update it if needed for you. Let me know in comment if you need more help with this topic.

SELECT sj.[name] AS [JobName]
	,c.[Name] AS [ReportName]
	,C.Path AS ReportPath
	,Su.Description AS SubscriptionName
	,su.LastStatus
	,su.LastRunTime
	,rs.SUBSCRIPTIONID
	,sj.JOB_ID
	,c.ComponentID
	,CASE 
		WHEN su.DataSettings IS NULL
			AND EventType = 'TimedSubscription'
			THEN 'Standard'
		WHEN su.DataSettings IS NOT NULL
			AND EventType = 'TimedSubscription'
			THEN 'Data Driven'
		END SubscriptionType
FROM msdb..sysjobs AS sj
LEFT JOIN ReportServer..ReportSchedule AS rs ON sj.[name] = CAST(rs.ScheduleID AS NVARCHAR(128))
LEFT JOIN ReportServer..Subscriptions AS su ON rs.SubscriptionID = su.SubscriptionID
LEFT JOIN ReportServer..[Catalog] c ON su.Report_OID = c.ItemID
WHERE c.[Name] = 'DAILYETLEXECUTIONREPORT'
ORDER BY [ReportName]

Query helps you:

1. Find the Job name associated with each SSRS Report Subscription
2. Find Last Status of subscription execution
3. Find type of Subscription – Data Driven or Standard
4. Using Job information we can do many more things.

SQL Server 2017 – Data Tools installation

This post to third and final part of SQL Server 2017 setup guide. We have got

1. SQL Server 2017 installed
2. SQL Server Management Studio 2018
3. Adventure Works SQL Database Sample
4. Adventure Works Multidimensional and Tabular Cube Sample

Now final piece is Tool for development of SQL Server 2017 solutions. We need BIDS (Business Intelligence Development Studio) or call it SQL Server Data Tools. Installation is quite simple.

1. Download SSDT for Visual Studio 2017 (15.9.2)
2. At installation it will ask for two things
   • Visual Studio 2017 instance if not then SSDT with Visual Studio 2017
   • All MSBI services (Integration, Analysis and Reporting)
3. Installer will do all the things for setting up Business Intelligence development setup within Visual Studio 2017, make sure Internet is connected.

That’s all, you are done with development part as well. Happy Coding!!

Comment in case of any hiccups and compliments are always welcome.