Calling SSRS report using either T-SQL or SSIS

[Update: 2009-12-30]

We got it working using SSIS.  Read this post:  http://geekswithblogs.net/LifeLongTechie/archive/2009/12/08/final-note-on-creating-pdf-using-ssrs-and-ssis.aspx

 

 

[Original post]

Would love to know if there is a way to kick off a SSRS report and pass some parameters into it via T-SQL or SSIS.  Unfortunately, we can't have MS SQL Enterprise edition.

Guess this is the true list of possible solutions:

  • VB script
  • PowerShell
  • WMI script
  • batch file
  • T-SQL

Sad thing is I know how to make this work with Crystal report, and I hope I can find a similar API for SSRS to make this work.  Any help is greatly appreciated.

 

 

[Update:  2009-12-01]

Looks like this is very promising:  http://ryanfarley.com/blog/archive/2006/01/27/15689.aspx

Using script task to get it:  http://www.tek-tips.com/faqs.cfm?fid=5919

Looks like the way to go is build with SSIS, use script component to call for each parameter.

 

  

Print | posted on Tuesday, November 24, 2009 2:53 PM

Feedback

# re: Calling SSRS report using either T-SQL or SSIS

Left by Paul at 8/18/2013 7:04 PM
Gravatar If you can link to your Reporting server you can setup a local Stored procedure and then from the server you can execute that

but you need to
setup a email subscription on the report
then find the report name from this Query
WITH SQlJobs (job_id, job_name, execution_time, execution_order)
AS
(
SELECT DISTINCT j.job_id
,j.name
,CONVERT(datetime, STUFF(STUFF(run_date,7,0,'/'),5,0,'/')
+ SPACE(1)
+ STUFF(STUFF(RIGHT('000000' + CONVERT(varchar(20), run_time), 6),5,0,':'),3,0,':'))
,ROW_NUMBER() OVER (PARTITION BY j.job_id ORDER BY CONVERT(datetime, STUFF(STUFF(run_date,7,0,'/'),5,0,'/')
+ SPACE(1)
+ STUFF(STUFF(RIGHT('000000' + CONVERT(varchar(20), run_time), 6),5,0,':'),3,0,':')) DESC)
FROM msdb.dbo.sysjobs j
INNER JOIN msdb.dbo.syscategories c ON j.category_id = c.category_id
LEFT OUTER JOIN msdb.dbo.sysjobhistory jh ON j.job_id = jh.job_id
WHERE c.name ='Report Server'
)
SELECT

x.job_name [SQl Agent Job]
,c.name [Source Report Name]
--,x.execution_time [Last Executed]
--,c.path [Report Path]
--,su.description [Description]
,'exec ReportServer.dbo.AddEvent @EventType=''TimedSubscription'' , @EventData='' '+ Convert(varchar(500),su.SubscriptionID) + '''' [Source SQLStatement ]
FROM SQlJobs x
INNER JOIN dbo.Schedule sc ON x.job_name = CONVERT(varchar(100), sc.ScheduleID)
INNER JOIN dbo.ReportSchedule rs ON sc.ScheduleID = rs.ScheduleID
INNER JOIN dbo.Subscriptions su ON rs.SubscriptionID = su.SubscriptionID
INNER JOIN dbo.Catalog c ON su.Report_OID = c.ItemID
WHERE execution_order = 1


the source sql statement looks something like this

exec ReportServer.dbo.AddEvent @EventType='TimedSubscription', @EventData='XXX-XXX-XXX-XXX'


the @eventData is the ID from the dbo.subscriptions table

that table has a field called Parameters that looks like this

'<ParameterValues><ParameterValue><Name>Value</Name><Value>You can automate Reports </Value></ParameterValue></ParameterValues>'

it is NText field


in an Sp you can do the following

supply a text based value
then update the subscriptions table parameters report to be like this



declare @value varchar(30)

set @value='You can automate Reports ' --Subsitute a value in here

select @value
update Subscriptions
set Parameters=

'<ParameterValues><ParameterValue><Name>Value</Name><Value>' + @value + '</Value></ParameterValue></ParameterValues>'
where SubscriptionID='XXX-XXX-XXX-XXX'


then
refire the job in the SP

exec ReportServer.dbo.AddEvent @EventType='TimedSubscription', @EventData='XXX-XXX-XXX-XXX'


so the Job would be something liek this



declare @value varchar(30)

set @value='You can automate Reports ' --Subsitute a value in here

select @value
update Subscriptions
set Parameters=

'<ParameterValues><ParameterValue><Name>Value</Name><Value>' + @value + '</Value></ParameterValue></ParameterValues>'
where SubscriptionID='XXX-XXX-XXX-XXX'


exec ReportServer.dbo.AddEvent @EventType='TimedSubscription', @EventData='XXX-XXX-XXX-XXX'

Your comment:





 

Copyright © Kevin Shyr

Design by Bartosz Brzezinski

Design by Phil Haack Based On A Design By Bartosz Brzezinski