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.