Documenting SSRS and SSIS Jobs in Job Activity Monitor

documentationsql serverssisssrs

We are running a SQL Server 2014 database using SSRS as a reporting tool. We have data coming in via SSIS jobs and linked servers. We have developers creating various SSRS jobs and scheduling them.

I am wondering if anyone has any recommendations on how to document all the jobs in the Job Activity monitor. My goal is to identify the SSIS jobs as well as the SSRS scheduled reports. I'd like to know what tables the SSRS jobs hit so I can make sure they are scheduled after any tables they hit are updated. Does anyone have a tool or a way they have created this documentation? I have become the accidental DBA in my group so any guidance would be appreciated.

Thanks!

Best Answer

This simple query on MSDB should get you started:

SELECT s2.subsystem AS [TypeOfJobStep]-- when subsystem = 'SSIS' then 'SSIS job step'
     , s2.command AS [JobStepCommand]
      , s.name AS [JobName]
FROM dbo.sysjobs AS s
JOIN dbo.sysjobsteps AS s2 ON s2.job_id = s.job_id

You can identify SSRS subscriptions because their name will be alternating alphanumeric strings with dashes. I think a SUBSTRING function could determine that.

SSIS job steps should show subsystem = 'SSIS', depending on how you execute your SSIS jobs.

As for identifying which tables your SSRS jobs hit, that will require you to document your SSRS reports. SSRS reports can access data through a stored procedure, or through an embedded query within the report. Once you determine which they are doing, you'll need to determine a way to link them to the jobs you previously queried.

This is the hard part, because SSRS reports can use multiple data sources and those data sources don't have to be on the same SQL server as your scheduled jobs. I don't know if this part is feasible.

Related Question