Is it possible to stop a subscription from mailing if there is no data contained?
I have got a report that mails everyday a set of raw data, I only have to mail the report if there is data in the report, is it possible to have a check inside the subscription to only email if the report has data? I am running a SSRS 2005 Reporting Enviroment.\ Thanks in Advance Jacques
April 23rd, 2010 12:02pm

I do this all the time, however I had to be creative. There are two relativey easy ways though. My favorite is the SSIS way of doing this. I have SQL Standard by the way and I do this with both 2005 and 2008. 1. SSIS with a new JOB. Create a new SSIS package First, you take your query for your report and reduce it to returning a 1 or a 0 so that you know if the report has data to report. You put that 0 or 1 into a variable. You check the variable to see if there is or is not data. If there is data, a 1, then you run the SSIS task to run a agent job for the scheduled report in the SQL Agent. You have to find the job, but there are a lot of ways out there to find the GUID that represents this scheduled job. I added a table in my database that recorded if the report had data on a given day so that I can query when my reports are getting kicked off. The last thing I did was write to this table. 2. Stored Proceedure with a new JOB. Create a new stored proc First, you take your query for your report and reduce it to returning a 1 or a 0 so that you know if the report has data to report. You put that 0 or 1 into a variable. You check the variable to see if there is or is not data. If there is data, a 1, then you run the sp_start_job for the scheduled report in the SQL Agent. You have to find the job, but there are a lot of ways out there to find the GUID that represents this scheduled job. I added a table in my database that recorded if the report had data on a given day so that I can query when my reports are getting kicked off. I added a table in my database that recorded if the report had data on a given day so that I can query when my reports are getting kicked off. The last thing I did was write to this table. The advantave of doing this in SSIS is that you do not have to worry about trapping if the report bombs in SSIS because SSIS will record that bomb and log it for you and you can set the job to e-mail you if there is any problem. If you do this in a stored procedure, you either have to loop around the Sp_start_job to see if it worked, or you have to set the agent job for the report to send you a notification if the job fails. I'd actually like to know if people have a better way of doing this, but this has worked for me. One caution, do not put multiple reports in this SSIS package or SP. Why? Because your users are going to LOVE this feature, they are going to ask you to create more of them, and eventually they are going to ask you to kick off these notifications at different times, or the SSIS package or SP gets too big. I know this from experience. We have 50 of them now!!! I can send you an SSIS package if you need it for an example, let me know if would like that. I honestly haven't tried the SP way, but I know it will work because I've performed the same functions described including loops to check job status with other business issues, just not this scenario. Let me know if you have questions or comments, Keith
Free Windows Admin Tool Kit Click here and download it now
April 23rd, 2010 7:39pm

@GreenMTNSun If we use the SSIS Method, then you have to make two copies of Stored Procedure and you have to maintain two copies of stored procedure and that will lead to maitianance pain. Because you have to make the changes twice if you need correction to SP( one in Data fetch for report SP and other in Sp returning 0 &1 ). ~~ Mark it as Answer if you find it correct~~
April 24th, 2010 12:31am

You can avoid making two copies of your SP. (I am assuming you mean the SP that the original report calls.) Add a parameter for where the stored proceedure is being called from. If SSIS calls it, return a 1 or 0, if the report calls it, return the data. I ran into that too. As far as the report is concerned, it will always return the same columns, so the report's dataset will be fine. The same as SSIS, it will always return the same column. So it makes maintenance easier.
Free Windows Admin Tool Kit Click here and download it now
April 24th, 2010 12:40am

Hi Keith, This is perfect! could you please send me the SSIS package so that i can use it as an example. Thanks a lot for the great advise Kind Regards Jacques Mail: jacques.bronkhorst@gdl.co.za
May 4th, 2010 1:34pm

I hope you recieved my email with the package. Take care, Keith
Free Windows Admin Tool Kit Click here and download it now
May 6th, 2010 2:46am

Hi Keith, I am also facing the same problem. I also want to stop a subscription from mailing if there is no data contained in it. Can you please send me that SSIS Package (based on it I can create a new one) to this email id : grkanth81@gmail.com I havent worked on SSIS till now but I have very limited knowledge on it. So hopefully I can get that right. Thanks Bablu
May 28th, 2010 3:58am

If you don't mind can I get this to you on Wednesday? I have work holiday weekend prep / system conversions I am working on. Once Wednesday come along I can give you a package that shows interaction with a stored proc that returns both report data and go / no go data for SSIS to react to. I think I've improved things since I last interacted on this here. So-k?
Free Windows Admin Tool Kit Click here and download it now
May 28th, 2010 11:59pm

Well Thats great.... I can wait till Wednesday........ Thanks for u r reply............
May 31st, 2010 4:25am

can you please send me this package also. Phil pmcleod01@hotmail.com
Free Windows Admin Tool Kit Click here and download it now
June 1st, 2010 4:45pm

Hi Keith, I appreciate if you could send me thos package. It would be very helpful as I have same scenario to do. Thanks in advance. Please send me your package to " anujahemant@gmail.com" Thanks, Anuja
August 24th, 2010 9:18pm

Done. Hope it helps. I send it out every few months to people through here. If someone else is interested in it, just let me know!
Free Windows Admin Tool Kit Click here and download it now
August 24th, 2010 9:24pm

Hi Keith, I got it. I am very thankful to you. I understand both SSRS and SSIs. So I should be able to do it, I think. So nice of you. Thanks again, Anuja
August 24th, 2010 9:34pm

Hi Keith, I have one question. For this thing to achieve , Do I have to do report subscription first and then create SSIs package. or I do not need to do Sibscription and just schedule a job in agent and then create ssis package? Please advise as I am good in SSIs and but not that good in SSRS. Thanks, Anuja
Free Windows Admin Tool Kit Click here and download it now
August 24th, 2010 9:43pm

You need a report posted to your SSRS server that has a subscription. What I do is send out a "one time" subscription on SSRS with all of my settings controlled in that subscription, and then I put that SQL Server job that the subscription created into the SSIS package that your talking about. When users want to change who gets the report, I change the one time subscription because its still a valid subscription. Does that answer your question?
August 24th, 2010 9:47pm

Hi Keith, Keith, Yes. thanks. that gives my answer. But when you say sql server job . Is that you have to schedule ssrs report job in sql server agent after subscription? as you schedule package in agent? I also sent you an email because somehow my Windows LoginId was not working for a while. sorry about that. Thanks, Anuja
Free Windows Admin Tool Kit Click here and download it now
August 24th, 2010 10:45pm

thanks Keith, I got it. Thanks for your help.I appreciate it
August 24th, 2010 11:14pm

Hi Keith, Report is working very well. Thanks a lot. I appreciate your help. Thanks, Anuja
Free Windows Admin Tool Kit Click here and download it now
September 21st, 2010 4:14pm

Keith, Can i get a copy of the SSIS for a sample. IT's greatly appreciated. email to ds@dataspecialist-llc.com Thanks
November 12th, 2010 9:32am

Hi Keith, I am looking for the solution that you explained here. Could you please send me SSIS package as an example? Thank you very much in advance. e-mail : elena_koyfman@yahoo.com Lena.
Free Windows Admin Tool Kit Click here and download it now
December 23rd, 2010 6:21pm

Hi Keith, this sounds like exactly what I was looking for! I'm new to SSIS and have been tasked with coming up with the very solution you're describing. If it's still available would you please send me a copy of this package - this could be extremely helpful - Thanks! neilphaby@gmail.com
July 2nd, 2011 4:24pm

Here is one another simple solution for the above problem http://social.msdn.microsoft.com/Forums/en/sqlreportingservices/thread/6fd7a41c-6210-46e5-aa06-989f08e1365e
Free Windows Admin Tool Kit Click here and download it now
July 2nd, 2011 4:49pm

This topic is archived. No further replies will be accepted.

Other recent topics Other recent topics