By Azzna


2016-06-23 14:02:44 8 Comments

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!

1 comments

@Arthur D 2016-06-23 14:21:40

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.

@Arthur D 2016-06-24 15:23:03

You're welcome and good luck! Please mark as answer unless someone provides a better answer.

@JzInqXc9Dg 2019-02-01 18:27:08

I have actually created a crude mobile app that displays each job and last execution status, etc.... I've been wondering how much interest there might be in something like this.

Related Questions

Sponsored Content

2 Answered Questions

[SOLVED] SQL agent jobs hung state (SSIS jobs hung)

  • 2017-03-19 18:59:11
  • chaithanya kumar
  • 3255 View
  • 0 Score
  • 2 Answer
  • Tags:   sql-server ssis jobs

1 Answered Questions

[SOLVED] SSIS - Fixed width flat file mapping tool/technique

  • 2015-10-16 18:16:18
  • Juan Velez
  • 7148 View
  • 7 Score
  • 1 Answer
  • Tags:   sql-server ssis

1 Answered Questions

[SOLVED] resource wait SQLCLR - wait_type CLR_SEMAPHORE by SPID 0

2 Answered Questions

[SOLVED] Investigating/Troubleshooting I/O Spikes

Sponsored Content