CODEWITHSUNDEEP
sql-serverreporting-servicesssisssrs-2008-r2
user3015739
user3015739

Reputation: 643

Executing SSRS Reports From SSIS

I need to execute a SSRS reports from SSIS on periodic schedule.

Saw a solution here :

https://www.mssqltips.com/sqlservertip/3475/execute-a-sql-server-reporting-services-report-from-integration-services-package/

But is there any other option in SSIS without using Script Task ? I don't quite understand the script and concern there could be some support issue for me.

Database : SQL Server 2008R2 Standard Edition

Any ideas ? Thanks very much ...

Upvotes: 0

Views: 9432

Answers (2)

Mark Seven
Mark Seven

Reputation: 46

You can create a subscription for the report that is never scheduled to run.

If you have the Subscription ID, you can fire the report subscription using a simple SQL Task in SSIS.

You can get the Subscription ID from the Report Server database. It is in the Subscriptions table. Use this query to help locate the subscription:

SELECT Catalog.Path
      ,Catalog.Name
      ,SubscriptionID
      ,Subscriptions.Description

FROM Catalog
   
   INNER JOIN Subscriptions 
   ON Catalog.ItemID = Subscriptions.Report_OID

In SSIS, you can use this statement, inside of a SQL Task, to fire the subscription:

 EXEC reportserver.dbo.AddEvent @EventType='TimedSubscription',@EventData=  [Your Subscription ID]

Hope this helps.

Upvotes: 0

MiguelH
MiguelH

Reputation: 1425

SSIS controlling the running of an SSRS in SQL Agent.

This assumes that the SSIS job will have updated a control record or written some other identifiable record to a database.

1. Create a subscription for the report.
2. Run this SQL to get the GUID of the report

 SELECT  c.Name AS ReportName
  , rs.ScheduleID AS JOB_NAME
  , s.[Description]
  , s.LastStatus
  , s.LastRunTime
FROM 
  ReportServer..[Catalog] c 
  JOIN ReportServer..Subscriptions s ON c.ItemID = s.Report_OID 
  JOIN ReportServer..ReportSchedule rs ON c.ItemID = rs.ReportID
  AND rs.SubscriptionID = s.SubscriptionID<br>

3. Create a SQL Agent job.

a. Step 1.

A SQL statement to look for data in a table containing a flagged record where the Advanced setting is "on failure end job reporting success"

IF NOT exists ( select top 1 * from mytable where mykey = 'x' 
and mycondition = 'y') RAISERROR ('No Records Found',16,1)

b. Step 2 

USE msdb 
EXEC sp_start_job @job_name = ‘1X2C91X5-8B86-4CDA-9G1B-112C4F6E450A'<br>

Replacing the GUID with the one returned from your GUID query.

One thing to note though ... once the report subscription has been executed then as far as SQL Agent is concerned then that step is complete, even though the report has not necessarily finished running. I once had a clean up job after the Exec step which effectively deleted some of my data before the report reached it!

Upvotes: 2

Related Questions