how to run SSRS reports using SSIS?

• manoj.ramaiah

  SSC Eights!

  Points: 864

  More actions

  May 25, 2015 at 7:40 am

  #300959

  I have a SSISpackage which have 10 execute sql tasks, which loads data into the 10 tabales. Using these 10 tables, I load

  data into 2 tables. These 2 tables are using to generate reports using SSRS. So we have creatad SSRS package which will generate report. So here what we are doing is we are loading data into

  those (10+2) tables. then running that report manually and sending that created excel report manully. Can we achieve this using SSIS only, so once data loaded into those 2 reporting table it will start generating reports. How do we achieve using SSRS?

  Thanks in advance

• SQL.AP

  Hall of Fame

  Points: 3244

  More actions

  May 25, 2015 at 11:01 pm

  #1800472

  Yes, we can achieve this using SSIS.

  You need to create a data driven subscription in your SSRS report which will extract the excel sheet and send it via Email.

  In SSIS, you can add SQL Task to trigger this subscription after completion of load in your tables.

  You can use this query on report server to execute your SSRS report subscription using SSIS:

  DECLARE @vchSubscriptionJobName AS VARCHAR(500)

  SELECT @vchSubscriptionJobName = Schedule.ScheduleID

  FROM dbo.ReportSchedule

  INNER JOIN dbo.Schedule

  ON ReportSchedule.ScheduleID = Schedule.ScheduleID

  INNER JOIN dbo.Subscriptions

  ON ReportSchedule.SubscriptionID = Subscriptions.SubscriptionID

  INNER JOIN dbo.[Catalog]

  ON ReportSchedule.ReportID = [Catalog].ItemID

  AND Subscriptions.Report_OID = [Catalog].ItemID

  WHERE [catalog].name = <<Report Name>>

  EXEC msdb.dbo.sp_start_job @vchSubscriptionJobName

  ____________________________________________________________

  AP