Getting Started with Adobe After Effects - Part 6: Motion Blur


Upload Image Close it
Select File

Browse by Tags · View All
#SQLServer 46
SQL Server 44
SSRS EXPERT 41
BI 36
#BI 33
brh 28
SSIS BEST PRACTICES 23
TSQL 19
MS BI STACKS 18
SSRS 17

Archive · View All
March 2011 24
July 2011 17
February 2011 15
April 2010 13
June 2011 11
May 2011 11
April 2011 11
July 2010 11
March 2010 8
December 2011 7

SSRS #60 – Steps to implement a data-driven subscription

Aug 13 2011 12:00AM by Sherry Li   

I am back from vacation, and have spent a couple of weeks catching up my workload. Now I am ready to finish the SQL Server BI Quiz “Mass Mailing Your Report”.

Here is my original blog, SSRS #55 - SQL Server BI Quiz “Mass Mailing Your Report”. Thanks to the following readers who have posted their answers on http://beyondrelational.com/quiz/sqlserver/bi/2011/questions/mass-mailing-your-report.aspx:

Underthefold
vshah555
alpesh5410
omvish

All of their answers mentioned about data-driven subscription engine in SSRS, but unfortunately, none has given details in implementation.

Since the quiz is all about the details in implementation, I have decided not to pick any winner.

In this post, I’ll blog the implementation using the data-driven subscription engine in Reporting Services.

I have seen developers using SSIS package to attach reports in emails. This approach requires spreadsheet templates and lots of scripting in SSIS.

With the data-driven subscription engine, you just need to create a SSRS report, and pass dynamic parameters to the SSRS report, and tell the data-driven subscription engine to send out the report as an attachment via email. There is no need for spreadsheet template to make the report pretty, and you can pass any values to the report parameters, and best of all, there is no need for any scripting.

There are four basic steps when working with data-driven subscription

  1. Create a SSRS report with the desired layout and parameters, and then
  2. Design a SQL table that will hold your dynamic parameters, and
  3. Pass the correct parameters to the SSRS report so the subscription engine does not reject them, and finally
  4. Tell the data-driven subscription engine to send the report (In my example, I’ll stay with email delivery option. With the file share option, it adds another layer of complexity to it, that is, using the correct service account to access the shared path when I get time, I’ll blog about it in a separate post.)

Step 1: Create a SSRS report with the desired layout and parameters

I assume that you already know how to create SSRS reports with parameters.

Step 2: Design a SQL table that will hold your dynamic parameters

In my example, I have the begin date, end date and the supervisor ID as my report parameters. When the supervisor ID is 1, I need send report to Supervisor1@company.com; when the supervisor ID is 2, I need send report to Supervisor2@company.com.

I can also use this same table to set the begin and end date for a month-to-date report, rather than just a daily report.

Step 3: Pass the correct parameters to the SSRS report so the subscription engine does not reject them

On the Reporting Server, find your SSRS report. Under tab Subscription, click “New Data-driven Subscription”.

In the Step 1 window,

  1. give the subscription a description.
  2. Select E-mail as the delivery option
  3. Select “Specify a shared data source”

On the Step 2 window, select the same data source your SSRS report uses.

On the Step 3 window, enter the SQL command as shown and click Validate button to make sure the command is good.

Table SUBSCRIBER.T_SUBSCRIBER_TEST stores all my parameter values shown in step 2. Change it to your own table.

In the Step 4 window, choose “Get the value from the database” for the following fields:

  • To
  • Cc
  • Reply-To

Also select Excel as the Render Format.

This is the fun part. When you click the drop-down box, a pick list showing all the fields from my table SUBSCRIBER.T_SUBSCRIBER_TEST showed up.

You can see that you can also make the email subject dynamic too.

On the Step 5 window,

  • Use field ParamBeginDate as the begin date
  • Use field ParamEndDate as the end date
  • Use field ParamSupervisor as the Supervisor

On the step 6 window, select the second choice.

This is the last window for the subscription wizard. Instead of triggering the subscription with a fixed schedule, we will trigger it in a SQL job step right after my SSIS package finishes the daily ETL process.

Selecting Once will disable the fixed schedule.

Last step, i.e. Step 4, trigger the subscription in a SQL job step right after my SSIS package finishes the daily ETL process

Since I use SSIS packages to automate 99% of my SQL tasks, there is no surprise that I did this last step also in a SSIS package.

This SSIS package is executed right after my daily ETL process. The primary purpose of this package is to trigger the above subscription I just created.

There are three things I need to take care before I start the subscription.

  1. I don’t want to send emails to users on weekends or during holidays. So I need to read in the holiday and the weekend flag. Then I used a Precedence Constraint to decide whether I need to continue to not,.
  2. The second thing I need to take care of is the table SUBSCRIBER.T_SUBSCRIBER_TEST. I need to populate it with the correct begin date and end date, and possible supervisor IDs.
  3. The last thing I need to take care of is to read the Subscription ID from the Reporting Server for my SSRS report.

This is the query you can use to get the Subscription ID from the Reporting Server for your SSRS report.

I save the Subscription ID to a user variable varSubscriptionID.

Finally, I use the AddEvent SQL command to trigger the subscription, of cause, with the Subscription ID (passed in by ?).

We are done!

Hope I didn’t confuse you or scare you away from using the data-driven subscription. If you are not comfortable with Integration Services, you do not need to use it. Simply triggering your subscription from a fixed schedule with a hard-coded Subscription ID in a SQL command is perfectly acceptable.

Tags: #SQLServer, SQL Server, SSRS EXPERT, brh,


Sherry Li
14 · 12% · 3827
9
 
0
Lifesaver
 
0
Refreshed
 
0
Learned
 
0
Incorrect



Submit

10  Comments  

  • Hi this is great! Do you have any sample code on how to script a subscription using rs utility 2008? And then how to implement and call the script? Thanks.

    commented on Apr 12 2012 5:05PM
    mejones
    2916 · 0% · 3
  • Would you be more specific about "script a subscription using rs utility 2008"?

    Sherry

    commented on Apr 13 2012 9:50AM
    Sherry Li
    14 · 12% · 3827
  • I need to write a script that will create a subscription in production when we deploy a new build instead of manually creating one on the report server. From what I have read you can do that with rs utility but I can't find any sample code.

    commented on Apr 14 2012 12:14AM
    mejones
    2916 · 0% · 3
  • Hi Sherry,

    The above information is very helpful. The question i have is "Is there a way to make the FROM field dynamic from SSRS itself? I know we can write custom code in SSIS but i was thinking if there is any way to do in SSRS itself.

    Thanks

    commented on Nov 28 2012 2:40PM
    V Shah
    257 · 1% · 176
  • I am not aware of any out-of-box capability in SSRS for the data-driven subscription query to be dynamic.

    Sherry

    commented on Nov 28 2012 6:45PM
    Sherry Li
    14 · 12% · 3827
  • Hi Sherry ,

    I totally loved your approach. I am new to SSRS and have a few queries. My requirement goes like this:

    • I have a table which stores the error data which is retrived from different excel sheets using an SSIS job.
    • I have to create an error report which has the error rows only specific to each excel sheet it is imported from and mail the same.
    • Mailing functionality i can achieve using the above article of yours but my question is how do i create different reports based on the data and how do i create an email subscription for each n every report when there can be 100's of reports?

    Please give me your suggestions.

    Thanks

    commented on Feb 10 2014 1:07AM
    MysteriousAngel1991
    1641 · 0% · 12
  • Hi sherry,

    I am using windows 8 professional system,i have done email setting in reporting services configuration manager as gmail account and the mail is not sending but the sql server agent job completed successfully.whats the problem.

    commented on Jun 27 2014 9:38AM
    ManishJohn
    2916 · 0% · 3
  • This comment is waiting for moderation.

    commented on Oct 30 2014 1:56AM
    resume.sathish
    2827 · 0% · 4
  • This comment is waiting for moderation.

    commented on Jan 9 2015 12:38AM
    hkumar
    3162 · 0% · 2
  • This comment is waiting for moderation.

    commented on Feb 11 2015 2:30PM
    brmes
    3162 · 0% · 2

Your Comment


Sign Up or Login to post a comment.

"SSRS #60 – Steps to implement a data-driven subscription" rated 5 out of 5 by 9 readers
SSRS #60 – Steps to implement a data-driven subscription , 5.0 out of 5 based on 9 ratings
    Copyright © Rivera Informatic Private Ltd Contact us      Privacy Policy      Terms of use      Report Abuse      Advertising      [ZULU1097]