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

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

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

@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~~

@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~~

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.

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.

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

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

I hope you recieved my email with the package. Take care, Keith

I hope you recieved my email with the package. Take care, Keith

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

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

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?

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?

Well Thats great.... I can wait till Wednesday........ Thanks for u r reply............

can you please send me this package also. Phil pmcleod01@hotmail.com

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

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!

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

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

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?

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

thanks Keith, I got it. Thanks for your help.I appreciate it

Hi Keith, Report is working very well. Thanks a lot. I appreciate your help. Thanks, Anuja

Keith, Can i get a copy of the SSIS for a sample. IT's greatly appreciated. email to ds@dataspecialist-llc.com Thanks

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.

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

Here is one another simple solution for the above problem http://social.msdn.microsoft.com/Forums/en/sqlreportingservices/thread/6fd7a41c-6210-46e5-aa06-989f08e1365e

Keith, If you are still out there, could you please forward out a copy of the package. Thankfully you ran into this problem first. sanhedrin @ hotmail . com

It's on the way!

Hi Keith, thank you for the great solution! Can you send me a copy of your SSIS package for reference as well? Thank you very much in advance! email: philiplw2@hotmail.com