February 18, 2015 at 12:54 pm
I've SSRS Data subscription that saves files into Windows Share location. The report runs at various times of the day.
As soon as the file gets saved in the windows share, an email needs to be generated and sends to the users about the windows shared location.
I’ve read (http://www.sqlservercentral.com/articles/Integration+Services+(SSIS)/93504/) that it is possible to create a Report (SSRS) using SSIS and then use SSIS task to email the link. I would do this if it’s a single report, but I've to create quite a number of reports.
Please let me know how to achieve this in SSRS.
Thanks in advance
February 18, 2015 at 3:07 pm
kpwaran (2/18/2015)
I've SSRS Data subscription that saves files into Windows Share location. The report runs at various times of the day.As soon as the file gets saved in the windows share, an email needs to be generated and sends to the users about the windows shared location.
I’ve read (http://www.sqlservercentral.com/articles/Integration+Services+(SSIS)/93504/) that it is possible to create a Report (SSRS) using SSIS and then use SSIS task to email the link. I would do this if it’s a single report, but I've to create quite a number of reports.
Please let me know how to achieve this in SSRS.
Thanks in advance
I suggest creating an email subscription that is scheduled a few minutes after the one that generates the files. It doesn't allow both to happen at once.
February 18, 2015 at 3:24 pm
Thanks for your suggestion.
If I am correct, I have to create 2 subscriptions for each report?.
If yes, then how to control the email subscription in the event of file share subscription get failed.
Thanks in advance.
February 19, 2015 at 9:17 am
Here is a query for the report server that shows subscription info for those that failed. You can use this as a starting point to create a query for the Data Driven subscription to run only for the ones that don't have a Last Status with the words "An error" in it.
SELECT sub.SubscriptionID, sub.laststatus, cat.[Name] AS RptName, cat.[Path], U.UserName, sub.Description, sub.DeliveryExtension AS scheduletype
,CASE WHEN sub.DeliveryExtension='SharedSchedule' THEN sc.name ELSE 'Unique' END AS ScheduleName
,res.ScheduleID AS SQLJobID, sub.LastRuntime
FROM ReportServer.dbo.Catalog AS cat
INNER JOIN Reportserver.dbo.Subscriptions AS sub ON cat.ItemID = sub.Report_OID
INNER JOIN Reportserver.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.sysjobschedules AS sch ON job.job_id = sch.job_id
INNER JOIN Reportserver.dbo.Users U ON U.UserID = sub.OwnerID
LEFT JOIN Reportserver.dbo.Schedule Sc ON Sc.ScheduleID = res.ScheduleID
Where sub.LastStatus Like '%An error%'
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply