Just a quick one tonight whilst I’m sat in Mcdonalds waiting for Breakdown Recovery 🙁 Obviously should have spent 50k on a successful man’s car instead of my window cleaner’s VW!
I recently had a requirement from a client who had a number of data-driven RS subscriptions; simple enough. However they wanted to only run the subscriptions on completion of their cube processing (which was controlled by an SSIS job). My solution (maybe not the most elegant but it worked for me) was to use the sp_Start_Job system stored procedure (MSDB).
As you may know whenever you set up a subscription in RS it creates a SQL Agent job to trigger the subscription. These jobs are given GUIDs as names which is less than helpful but you can work out which is your job by looking at the create date and the schedule you created for the subscription. Once you’ve found it you can re-name it with a more meaningful name and also delete the timed schedule as this is not required.
Next create a simple control table to hold the job names that you wish to trigger. Then create a simple SSIS Package that loops on the control table and use an Execute SQL task to run the sp_Start_Job stored procedure passing the job name as parameter
Finally add this package to your main batch processing package.
When I have a moment I’ll upload the package. Be interested to hear from people who have done it differently…
