none
home growing my own data driven subscription job

    Question

  • Hi.  We run std 2008 and I'd like to develop a lightweight data driven subscription like job that triggers creation of a few hundred master reports (about 15 sub reports per master) automatically once every quarter.  If ssrs's master to sub plumbing creates some of the page and data "breakage" issues I've seen, I may resort to running the subs directly from the job bypassing the master report and perhaps enlisting some other software to glue the individual sub reports together in pdf form for each customer/date.  The idea will be for directories (RS or file system) to be created with customer and datestamp info that makes it easy to find a particular customer's report(s).  I've seen a couple of posts on this subject and will start visiting those posts in more depth shortly.  If the communits has anything to add please share.  Any type(s) of container (eg ssis) are fair game.   I may move this post over to the SSIS forum if necessary.  

    db042188

    Wednesday, July 17, 2013 6:28 PM

Answers

All replies

  • these are older articles but it appears that these guys essentially tapped into RS's subscription repository table(s), modify data there as needed and (re)invoke the subscription as needed.  All from a proc.  I'm a little concerned that they didnt use .net which in theory would be more likely to work regardless of the formatting changes that can occur in RS's repositories from release to release.   I just glanced at these articles so its possible I dont understand them yet.  On the surface it appears that they are focused on emailing rather than archiving reports.  But I need to look at them more closely. 

    http://sqlblog.com/blogs/greg_low/archive/2008/08/13/data-driven-subscriptions-in-sql-server-2005-standard-edition.aspx

    http://www.codeproject.com/Articles/14085/Data-Driven-Subscriptions-in-SQL-RS-2000-Standard


    db042188

    Wednesday, July 17, 2013 7:02 PM
  • this is also older but hopefully still relevant.  It uses classes exposed by RS to run and save reports, I think.

    http://www.codeproject.com/Articles/15555/Generating-PDF-reports-programmatically-using-SQL


    db042188

    Wednesday, July 17, 2013 7:15 PM
  • similar to last post but apparently there is an rs scripting file type that can be run from the command line.

    http://bluesmock.net/content/run-sql-server-2008-ssrs-report-windows-command-line-parameters


    db042188

    Wednesday, July 17, 2013 7:26 PM
  • I'm not sure why this post was marked as answered but here is what I did in a prototype...

    I created an ssis pkg that follows these steps:

    1) sets variables that are used pkg wide

    2) does our usual checking to determine if the pkg should run

    3) loads a list of customers who need to get reports today into a table

    4) loads the latter into an ado recordset destination

    5) sets global rs param values in ssis variables, ie those that are applicable to all customers 

    6) iterates thru the items created in #4 to call an execute process task component that executes rs from the command line and passes a list of params, including the rss script file location

    7) does cleanup

    I have to add logic that will make this more dynamic but have some degree of confidence there now that I see the essential plumbing working. 

    I had to add the -l parameter to the command line in order for this not to time out.  My sense of RS is that any time you add functionality (eg master calling subs or running from a command line or adding params etc) you are in for a performance hit.   Also, one of my RS params needed a workaround where instead of passing its empty string value via the command line, I set it to null (NOTHING) right in the rss script.  In this particular pkg, its other allowable values arent used.

    I'm happy that this all works but very concerned about what appears to be run times that border on unacceptable.  This has to run each quarter for over 400 customers x  20 sub reports.   In those 20 subs there are actually only 7 different RS reports that are passed different permutations of params.   


    db042188


    • Edited by db042188 Monday, July 22, 2013 8:02 PM spelling
    Monday, July 22, 2013 8:00 PM
  • it appears that rss doesnt like directory nodes (folders) whose names start with a number.   My file name (fully qualified pdf)  had a folder name about 3 deep in the path that started with digits of year.  The error had something to do with finding the path.  When I put the word year in front, it ran.

    Also, it is unclear to me at this point how to pass a non rs parameter to the script, ie one that isnt passed to the report.  I'll try adding it (filename)  to the global list etc in hopes of an extra var not causing the report to abort. 


    db042188

    Tuesday, July 23, 2013 3:52 PM
  • passing a var not known to the actual report didnt work.

    db042188

    Tuesday, July 23, 2013 3:55 PM
  • so where i'm headed with #6 is to pass one recordset row (probably sorted) per scheduled report to a set of five (maybe 4) components contained within a foreach loop that iterates thru the recordset.

    Each recordset row will contain a customer name, quarter (eg 2013Quarter1), single varchar based list of params (which includes rss file name) in rss format, target pdf report file name.

    Each of my 7 RSS files will write its pdf report file to the same work directory folder with the same generic name as the other 6.

    The 4 or 5 components will do this:

    1) create a folder for the customer (specified in recordset) if it doesnt already exist (using file system task)

    2) create quarter (specified in recordset) folder under latter if it doesnt already exist

    3) use process task to run RS using param string coming in with recordset

    4 and maybe 5) either copy and delete or simply move generic file from work directory to renamed file in directory indicated by customer name and quarter and using target file name for renaming purposes.

    This all probably means that I'll need to move up the original step 5 called "sets global rs param values in ssis variables, ie those that are applicable to all customers " so that those settings can be leveraged when I record a single varchar based param list for each cust/report combo in this quarter's run.

      

     

    db042188



    • Edited by db042188 Wednesday, July 24, 2013 1:44 PM more info
    Wednesday, July 24, 2013 1:10 PM
  • to get around some dynamic file system folder naming issues at the customer level, I added some logic that replaces slashes etc in the customer name mostly with semicolons for naming folders, and for good measure used a rank to rename those situations where a tiebreaker became necessary as a result.  But I left the original cust name intact when passing it as a param to the various reports.  The tie breaker rename just tacks on an underscore and zero filled rank to the folder name for any cust whose rank is 2 or more.

    For now, the chars involved in this replace are /,   \,    :,    .,  ?,    *,   |,   ",    <,    >

    All but the period are currently getting replaced by semicolons.  Because windows is going to do it for me anyway, I replaced periods with an empty string so I could get control of that situation before the pkg aborts.


    db042188

    Thursday, July 25, 2013 5:28 PM
  • one thing I wish I had anticipated in the plumbing of rss between ssis and rs is the lack of flexibility (somewhat) of the datasource to be used.   I'll need to think about that and will repost here.

    db042188

    Wednesday, November 06, 2013 8:29 PM