Sign in
 
Home20122008 R2200820052000LibraryForums
SQL Server >
> 
> 
Data driven subscription for a big pile of reports?

Answered Data driven subscription for a big pile of reports?

  • Friday, April 27, 2012 8:45 PM
     
     
    Sign In to Vote
    1
    Hi there,

    I have a really basic task to do, but for some reason I can't find any articles or help from Google on how to solve it.
    I have a report that is about 80 pages long created in Report Builder to be rendered in PDF format with fancy graphics and bar charts.  Now the layout of this report is fixed, but the data varies based on a single parameter supplied to it (the Respondant ID).  Now I need to run the report for 800 different Respondant IDs.

    So I can spend 5 days manually creating a report for each ID, or I can use a data driven subscription to do this for me.

    Well, at least I THOUGHT I could use a data driven subscription to handle this task.  See, if I run one or two reports through
    this subscription everything is fine, but as soon as I scale up the subscription to handle 15 (let alone 800) bad things start to happen.
    In the ReportingService log I see error messages like

    ERROR: Error occured processing subscription 0cb175ff-72f8-43ec-94a2-4f07ac417d14: Thread was being aborted.
    ERROR: Queue item has unhandled exception. System.Threading.ThreadAbortException: Thread was being aborted.

    Is the silly thing trying to run all 15 reports at the same time?  That's crazy - no wonder it is having resource problems.

    So I am hoping some kind soul can help me here.

    1)  Is my interpretation of this message correct?  Is it really trying to run all the reports at once?  If so - why would SQL Reporting have such uselessly optimistic behaviour?  Or does SQL Reporting TRY and run it in manageable chunks, but on occasion goofs up on estimating the resources that are required?  Is there any way to make it more pessimistic (i.e. don't process more than 2 reports at the same time)?

    2)  If I am correct in 1, then HOW excactly do you get a subscription to handle such a straight forward task?  Is there a setting I am missing?  Am I barking up the wrong tree here, and the proper solution has nothing to do with data driven subscriptions?

    Thanks in advance

    Dan
     

All Replies

  • Friday, April 27, 2012 11:08 PM
     
     
    Sign In to Vote
    0
    Sorry - I forgot to state in my original post.  I am running SQL Reporting Services 2008 R2
     
  • Saturday, April 28, 2012 11:28 AM
     
     
    Sign In to Vote
    0

    Hi Dano,

    I haven't run into an issue like yours.  Are the reports being emailed?  Here is an article that may help you with an alternative solution.  You can use SSIS to build out each of the reports and store them in a SharePoint library.  You could add an email task to the package and run this in a loop instead of a batch (since batch seems to be your problem), to create each report, store it in SharePoint (or file system) and then email the report.

    http://whitepages.unlimitedviz.com/2012/04/how-to-automate-sharepoint-report-creation-with-ssis-and-ssrs/

    I know this isn't a direct answer, but I hope the information in the article will be helpful for a possible workaround.

    Cheers,

    Martina

    Martina White

     
  • Saturday, April 28, 2012 11:54 AM
     
     
    Sign In to Vote
    0

    I found something else that may be relevant about processing large reports.

    http://technet.microsoft.com/en-us/library/ms159638.aspx

    The thing that caught my was:

    "If you want to use e-mail report delivery, configure the subscription to include a link. Avoid sending the report as an attachment."

    If you are emailing the report, perhaps that is what is causing the error.  If you are not already doing so, try running your subscription to store it in a file share and see if it still gives you an error.

    Cheers,

    Martina

    Martina White

     
  • Monday, April 30, 2012 1:44 PM
     
     
    Sign In to Vote
    0
    Hi there Martina,

    Thanks for the responses.  My situation is really-really basic, which is why I am so perplexed that I can't find any Google help.

    I am using SQL SERVER 2008 R2 Enterprise edition.

    We are not using sharepoint.

    We aren't even emailing the reports.  All we want is the reports to be made and then dumped into a file directory.

    The subscription works, if the number of reports are really small.

    Should a subscription be able to handle this task?  Have you ever tackled a task like this using a subscription solution?
     
  • Monday, April 30, 2012 1:49 PM
     
     Proposed
    Sign In to Vote
    0

    I have not tackled a task that size using a subscription solution, but the subscription ought to be able to handle it.   For troubleshooting purposes I recommend you try timing how long it takes to run one report through the subscription and then two reports through the subscription.  If it takes substantially less time to run two than one, it probalby is rendering them all at once which may be the reason that it gives and error when running a larger number.  If so, you could use SSIS to run the script task described in the article above, but put the script task in a loop which only renders one report at a time.

    Hope it helps.

    Martina

    Martina White

     
  • Tuesday, May 01, 2012 5:48 PM
     
     
    Sign In to Vote
    0

    Hi there,

    I did some benchmarking - the difference between 1 and 2 reports is trivial.  The subscription seems to process reports in 4 unit chunks, but when it gets to about 15, then 2 of the reports are skipped.

    I went through the article but the solution seems to be pretty deeply integrated  with Sharepoint which we aren't using.  I also Googled SSIS and scripting subscriptions but without any success.

    I just can't seem to shake the feeling that I am going about this all wrong - that there is a hidden setting somewhere.  How can such a basic, straightforward task be so horribly, horribly complicated?  i.e.  Here is a Report - run 1 for each of the 800 numbers stored in this table.  Save them to X.  That's it, that is all I am trying to do.

     
  • Wednesday, May 02, 2012 1:22 PM
     
     Answered
    Sign In to Vote
    0

    You are not missing anything. Data driven Subscription should be able to do this.  There is likely something about the complexity of this particular report which is throwing the error when trying to render multiple versions at once. 

    For this reason I suggest you follow the concepts in the article and build an SSIS package which imports the Respondent ID into a variable, the loops through each Respondent ID one at a time in a For Each Loop and publishes the PDF to your file system one at a time, before going to get the next Respondent ID.  The approach in the article is not restricted to SharePoint – it can be used with native mode to read through a list of parameter values and store them in a file system.  You can skip Steps 1 and 2 and modify Step 3 onward to suit your particular circumstances. 

    Your SSIS package will likely follow these steps:

    1. Uses an Execute SQL task to populate a variable of Object type with the Repondent IDs

    2. Passes the variable to a For Each Loop.  Set the ADO object source to your Object variable being populated in the first step.

    3. Map a new variable of the correct type (int or string for the Respondent ID) in the For Each Loop editor to the field being returned in the collection

    4. Inside the loop add a script (follow the article to customize your own script) which calls a Web Service, renders the report and stores it in the file system.

    In doing the steps in a loop, rather than all in one go like the articles example does, you may be able to circumvent whatever issue the data driven subscription is running into rendering multiple complex reports.  Try it and let me know how you make out.

    Cheers,

    Martina

    Martina White

    • Marked As Answer by Dano1967 Wednesday, May 02, 2012 1:41 PM
    •  
     
  • Wednesday, May 02, 2012 1:29 PM
     
     
    Sign In to Vote
    0

    This forum post may shed some light on why you are getting the error.  It looks likely that it is, indeed, the complexity of your report.

    http://social.msdn.microsoft.com/Forums/en/sqlreportingservices/thread/9a389066-0f65-4dd6-915f-687b35d4f9ab

    I think perhaps the solution I mentioned above may provide a suitable workaround to your problem.

    Martina White

     
  • Wednesday, May 02, 2012 1:41 PM
     
     
    Sign In to Vote
    0
    Thanks for your help - I'll give this a try and let you know how it goes
     
  • Friday, May 04, 2012 7:42 PM
     
     
    Sign In to Vote
    0

    Just thought I would update how I resolved this situation.

    Basically going from the idea of calling Reporting Services from SSIS, I found this article

    http://36chambers.wordpress.com/2011/06/03/reporting-services-2008-creating-reports-programmatically-without-using-a-reportviewer/

    And did the method outlined in "Using the WebService"

    Then I just built a small launcher application in C#.Net that called this routine from a loop updating the parameters as needed.

    Not only did this work without a hitch, but it is CRAZY fast - faster than the subscription method.   Before it took a total of about 3-5  minutes to render and save a single report.  Now I can render and save 50 reports in about 8 minutes!