0

I have a .RDL report which I designed in BIDS and have deployed to my report server. The report asks for three parameters before viewing report: Year, Month and Customer ID. The report works great and does exactly what it is supposed to.

While I used to run each report individually because there were 2-3 customers, now there are 30+ customers who receive the report, so I wanted to switch to a more automated fulfillment method to get the reports generated. After doing some research it appears that a using Report Manager to create a "Data Driven Subscription" (DDS) using the "Windows File Share" option gives me the capabilities I need.

As part of creating the DDS, I created a table called [Subscription] which is a table containing one row for each customer receiving the report and has the following columns:

  • Year
  • Month
  • CustomerID
  • FileName
  • FileLocation
  • Overwrite
  • Format

...so through using the DDS Wizard in Report Manager, I was able to successfully set up a Data Driven Subscription (which is linked to various columns in the [Subscription] table) which creates a new report for each customer in the [Subscription] table, saves [and overwrites, if necessary] it in a location of my choosing as a PDF (specified in [Subscription].[FileLocation], or the FileLocation column of my table for each row), and runs every minute (I plan on changing frequency to once a week, eventually).

This works flawlessly, giving me a new set of 30 reports in the directory of my choosing, with each report having a name I assigned in the FileName column of my table. Exactly what I was looking for.

HERE'S THE PROBLEM: When I update the FileLocation or FileName (or anything, really) in the [Subscription] table - it doesn't pick up the changes right away. Sometimes it doesn't even pick it up at all (for example I updated the [ReportName] column for one customer from Report_711622 to SpecialReport_711622, so that the output file for that customer should be named SpecialReport_711622 while all of the other reports should be called Report_XXXXX [no Special prefix]. But the file name of report for Customer 711622 remains the same!

It's almost like the job only see's what it needs to do once a day, and then does not go back and reference the [Subscription] table until I leave for the night, then when I come back in the morning it picks up the change.

Since I am about to scale this process out to a large customer-base using a different report, I need to be able to make edits to the [Subscription] table and have them get picked up by the Data Driven Subscription immediately (and if not immediately, at least a fixed interval of time that I can adjust, so that I can know 100% when the change will get picked up).

Does anyone know what's causing my lag? How do I change it so that updates to the Subscription table get picked up regularly? I'm also having issues with creating new DDS on other reports (following the exact process outlined above) - I've created the subscriptions, for every minute, and it says they are running and the number of outputs match the number of customers with 0 errors, but there are no files in the drive I specified (or anywhere else I've looked, for that matter).

Any help would be greatly appreciated!

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
ezbz
  • 1
  • 3

2 Answers2

1

I think the answer lies in the mechanism SSRS uses. There are a few places "lag" can occur.

The subscription is in fact an SQL Agent job which creates a record in the Event table. This table is a queue that SSRS checks to do scheduled tasks.

There is a small amount of time between the moment the subscription creates the Event record and the moment SQL reads it and starts creating the dataset for your DDS. The creation of the DDS dataset takes some time, too. In this time, the subscription will be in the Pending state. If you change anything in the data during this time, The subscription will still use the old data as report parameters. So obviously you will not notice your change until the next scheduled run.

Which brings me to the following: if a subscription is still being run and the next schedule kicks in (chances are, because yours runs every minute), the engine will not execute it, but wait for the next subscription schedule, and so on. So that's another possibility of lag - and cause of missing reports for a certain schedule minute. The subscription processes reports sequentially, one row from your DDS recordset at a time. Again, this takes some time. You can also see that in the subscription window when it says: # of # processed.

I suggest you look at the Event table in the database ReportServer during an execution. Also the ExecutionHistory views (there are 3) may be interesting. A scheduled run shows up as a RequestType = 1 and generates one record for each report. You can see the exact timing and parameters of each report that is run in the subscription. You may be able to extract the data you need to resolve your other issues.

EDIT: Here is a more elaborate guide to DDS data and events http://blogs.msdn.com/b/deanka/archive/2009/01/13/diagnosing-and-troubleshooting-subscriptions.aspx http://blogs.msdn.com/b/deanka/archive/2010/02/16/troubleshooting-subscriptions-part-ii-using-the-report-services-trace-log-file.aspx

H B
  • 701
  • 6
  • 9
  • Thanks @H - this is all really really helpful information. The part about 'missing reports' due to high-frequency intervals is really helpful for me to know as I begin to roll this system out accross the enterprise. – ezbz Nov 19 '13 at 19:47
  • I guess the part I'm still struggling with, though, is why aren't my changes to the [Subscription] table on my server (like changing the filename, the filepath, etc) being picked up when my 30 reports successfully run? It's like they are pulling the 'old' filename from some temp table - - when in fact, I've updated it and the filename should be changing but it's not. Any thoughts? – ezbz Nov 19 '13 at 19:50
  • @H - it's almost like there is a phantom DDS running in the background. I changed the frequency of my every-minute-30-report-job to every 5 minutes - and the reports are still updating every minute. Could there be something going on behind the scenes on the report server that [for some reason] isn't showing up in my list of DDS subscriptions? – ezbz Nov 19 '13 at 20:20
  • That's really strange.. All I can think of is, you need to finish the DDS wizard entirely to apply changes to the schedule. Changing the schedule and OK-ing is not enough. (Got me a few times, too). 5 minutes is a good idea - it probably eliminates overlapping schedules. – H B Nov 19 '13 at 20:53
  • @H I just deleted and readded the three DDS subscriptions to get rid of any 'mid-step' glitches (including the original one referenced above). When I make them they don't start right away, but when I come in next morning they were running grt. Also when I made changes to the table parameters feeding into DDS, they don't get picked up right away - but are working once I come in the next day. I would really rather not have this kind of lag - but as long as it is a consistent lag that's something I can live with. I wonder what it is about overnight? Any thoughts? Thanks for all your help. – ezbz Nov 21 '13 at 12:44
  • Found a great troubleshooting guide to DDS here http://blogs.msdn.com/b/deanka/archive/2009/01/13/diagnosing-and-troubleshooting-subscriptions.aspx and here http://blogs.msdn.com/b/deanka/archive/2010/02/16/troubleshooting-subscriptions-part-ii-using-the-report-services-trace-log-file.aspx I'm afraid I cannot be of much more help at the moment as I don't have access to a SSRS Entrprise box to test a DDS right now. – H B Nov 21 '13 at 21:31
  • @H thank you for all your help. I will continue to troubleshoot and update this post if I find a solution. If there is no update, I have not figured it out. Thanks again. – ezbz Nov 22 '13 at 15:04
0

Could this "Double-Hop" problem be the source of my issues? I'm so stuck on this one! The Double-Hop Problem - MSDN Knowledgecast

ezbz
  • 1
  • 3