Configuring SMTP and SSRS for Data Driven Email Subscription...

Configuring SMTP for Reporting Service Data Driven Email Subscription

Data Driven reports are the reports which takes the Input from the Database tables. Since the data are taken as the Input to the Subscription dynamically @ runtime. We call them as Data Driven Email Subscription. Advantage of the Data Driven are, User can easily Update or Insert the User Email ID, Rendering Format of a Report such as PDF, Excel, Word etc... and even the Input Parameters can also be set from the Database tables.

Step 1: Configure the SMTP - Email Setting in SSRS Configuration Manager as shown below. Once done click to apply. If the SMTP server is fine, then the Result will be "Your Configuration has been Updated"

Step 2: Open the Report Manager and click on the report to configure the Report for Email Subscription

Step 3: Click on Data Sources - again Click on Custom Data Source - Radio button. In the Connection String add the respective Connection string. In Connect Using set the "Credentials Stored Sec In the Report Server". Apply the User Name and Password and check the Window Credential Checkbox at the bottom

Step 4: Click on Subscription. Create a table as shown below
Create table EmailSubscription
(
           toList  Varchar(Max),
           ccList Varchar(Max),
           bccList Varchar(Max),
           replyList Varchar(Max),
           IncludeReport Bit,

           RenderFormat Varchar(50),
           Priorities Varchar(50),
           Subjects Varchar(Max),
           Comments Varchar(Max),
           IncludeLink Bit,
           Starttime Varchar(10),
           Endtime  Varchar(10)
)

 

Step 5: Insert the toList, bccList, ccList and the rendering format of the Report

Insert EmailSubscription Values ('albertdaniee@gmail.com', '','','',1,'Excel','High','Sales Report…', 'Yearly Sales Details...',1, '2013-09-23 00:00:00','2013-09-30 00:00:00')

 

Insert EmailSubscription Values ('albertdaniee@gmail.com', '','','',1,'Excel','High','Sales Report…', 'Monthly Sales Details...',1, '2013-09-23 00:00:00','2013-09-30 00:00:00')

Insert EmailSubscription Values ('albertdaniee@gmail.com', '','','',1,'Excel','High','Sales Report…', 'Daily Sales Details...',1, '2013-09-23 00:00:00','2013-09-30 00:00:00')

Step 6: Now configure the Email Subscription by Clicking the New - Data Driven Subscription as shown

Step 7: Choose the Method of Delivery as Email and Click next to select the Email Subscription table from the respective database.

 

Step 8: Add the Connection String with respect to the Email Subscription table and Click Next on Completion

Step 9: Write the Select Query as "Select * from EmailSubscription (NOLOCK)"  and press the Validate Button at the bottom of the screen - Step 3. Click next once done.

Step 10: Select the all the Radio Button as "From Database". Now we can easily configure the Email Subscription tables column with these Dropdown list.

 

Step 11: this step is more Important step, where the Input Parameters can be easily passed from the Database dynamically.

 

Step 12: Create a schedule using SQL Agent which is Automatically created by choosing the below options

Step 13: Create the Schedule for the given data driven subscription.

Step 14: Data Driven Subscription is Created. let's wait for another 30 min for the Emails to Pitch in the Inboxes :-).

 

Did you received the Email, If No, what needs to be done next

Step 15: Go to start Menu, open the MCAFEE from the Program Files, click on Virus Scan Console as shown below

Step 16: Right click on Access Protection and Click on View Log. Log file will be shown as below

Step 17: Log file shows the ReportingServicesService.exe is getting blocked by the MCAFEE

Step 18: I Received the Email once the above Executable is added in the Non Blocking section of MCAFEE

 

Happy Coding. Willing to Code Always :-)

 

Regards,

Daniel J,
Dataware & Data Mining Architect...