Data Driven Subscription Standard Edition


Data Driven Subscription (DDS) is SSRS feature available in SQL Server Enterprise Edition only. It allows rendering many copies of the same report based on combination of parameters and deliver them by query defined destinations (emails or file location). DDS checks if report has data and pushes that like notification. That means other SQL Server editions lack of that feature. For that reason (+ customization), I have created small solution which allows filling the gap.

As always, implementation is build on assumptions (in other words, based on specific interfaces).  Here is a list of assumptions:

  • Not every report needs DDS.
  • If report requires DDS, it must:
    • have parameter UserId
    • have subscription with dedicated shared schedule. That schedule should be based on day with specific time, for example 8:00AM (user local time, not server time).
  • DDS report can have many subscriptions. Shared schedule can be used in many subscriptions
  • Dedicated shared schedule should be:
    • named with prefix, for example, “DataDrivenSubscription.”.
    • paused, which means SQL Server would not call that schedule.
  • UserId parameter should be based on generic lookup list of users. For example, database view [dbo].[vw_ReportSubscribers]. It should have the following fields:
    • Id. It is used as DDS report parameter.
    • Email. It is used for report delivery
    • DisplayName. Is used as part of email invitation
    • TimeZone. It allows sending report based on end user local time
  • As far as shared schedule is paused, another SQL Server job has to invoke report rendering. That managing job should use frequent schedule (every 5-10 minutes) simulating long pooling. That job contains one step with SSIS package which loops user parameter collection and makes a decision which UserId report render and send.
  • DDS should evaluate report data. For that reason the report subscription [Comments] field should have SQL query which check if report has data. If report has no data – it should not be rendered, no report data -no delivery.

The following diagram describes the process:

DDS diagram

Every X minutes SQL Server agent job runs SSIS package which iterates through users collection. Stored procedure, let’s call it [dbo].[GetRenderSettingsData], checks if report has data for that user and generates xml which is used by Unit Testing SSRS Tool to render the report.

When report’s file is rendered by the tool, it is sent by SSIS package email task to end user. The delivery is based on user local time (this feature is unique for this solution and does not exist in SSRS Enterprise Edition).

Advertisements

About Andrew Butenko

https://www.mcpvirtualbusinesscard.com/VBCServer/a9939be0-be6f-4249-a775-6665eccff2e4/card https://www.microsoftvirtualacademy.com/Profile.aspx?alias=530492
This entry was posted in SQL, SSIS, SSRS and tagged . Bookmark the permalink.

One Response to Data Driven Subscription Standard Edition

  1. csadmin1 says:

    You can also use SRQ-RD, a third-party solution, create and run data-driven schedules without having to upgrade to SQL enterprise. It also comes with event-based scheduling (triggers the report on events including database changes, file changes, emails received and so on).

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s