February 4, 2011 at 9:50 am
We a scheduled job within SQL 2008R2 that uses SSIS to retrieve data from our legacy systems and update SQL tables and also re-processes an SSAS cube. We have defined report schedules within SSRS to run all our daily, weekly, and monthly reports. Roughly 300 reports per day.
Our problem is if the SSIS jobs fail to load the data (usually yesterdays transactions), is there any way to NOT run any of the reports. Currently, our reports run on a predetermined schedule and I really don't want them to run if the data is not updated in our SSAS environment.
Anyone know of a way to not have SSRS schedules (or reports) run if a SSIS job fails?
Thanks
Randy
February 4, 2011 at 11:22 am
The short answer is yes.
You need to put some error handling in your SSIS package that will execute a SQL script when the job fails.
All SSRS report subscriptions ultimately become SQL Agent jobs. Take a look at the following script. I use it to help me figure out which report subscriptions translate into which jobs, and the status of those jobs. Run it against your reporting services database.
SELECT cat.[Name]
,cat.[Path]
,res.ScheduleID AS JobID
,sub.LastRuntime
,sub.LastStatus
,sch.next_run_date
,sch.next_run_time
,LEFT(CAST(sch.next_run_date AS CHAR(8)),4)
+ '-' + SUBSTRING(CAST(sch.next_run_date AS CHAR(8)),5,2)
+ '-' + RIGHT(CAST(sch.next_run_date AS CHAR(8)),2)
+ ' ' + CASE WHEN LEN(CAST(sch.next_run_time AS VARCHAR(6))) = 5
THEN '0' + LEFT(CAST(sch.next_run_time AS VARCHAR(6)),1)
ELSE LEFT(CAST(sch.next_run_time AS VARCHAR(6)),2) END
+':' + CASE WHEN LEN(CAST(sch.next_run_time AS VARCHAR(6))) = 5
THEN SUBSTRING(CAST(sch.next_run_time AS VARCHAR(6)),2,2)
ELSE SUBSTRING(CAST(sch.next_run_time AS VARCHAR(6)),3,2) END
+ ':00.000'AS NextRunTime
,CASE WHEN job.[enabled] = 1 THEN 'Enabled' ELSE 'Disabled' END AS JobStatus
FROM dbo.Catalog AS cat
INNER JOIN dbo.Subscriptions AS sub
ON cat.ItemID = sub.Report_OID
INNER JOIN dbo.ReportSchedule AS res
ON cat.ItemID = res.ReportID
AND sub.SubscriptionID = res.SubscriptionID
INNER JOIN msdb.dbo.sysjobs AS job
ON CAST(res.ScheduleID AS VARCHAR(36)) = job.[name]
INNER JOIN msdb.dbo.sysjobschedulesAS sch
ON job.job_id = sch.job_id
ORDER BY cat.[Name]
GO
You could use this to create a table of report subscriptions that you do not want to run if your SSIS package is unsuccessful. Look at the last column of output on my script. It refers to the msdb.dbo.sysjobs table "enabled" column. Set the "enabled" column in msdb.sysjobs to 0 for the report not to run.
You may even want to start your SSIS package by disabling the jobs, then enable them as the package completes.
Once you get it working, please post your script back here.
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply