Custom Scheduler - SQL Server Reporting Services

• Download Executable - 20 Kb
• Download scripts

Introduction

I was currently working for a company name Hermz Software which needed to create reports using SQL Server Reporting Services as it's reporting tool. As far as creation of reports is concerned, I didnt have much problems!! However, the delivery of the reports were to be done based on the following snapshots.

This snapshots can easily be obtained from the Outlook task creator. Well, not a big deal, as SSRS had it's own scheduling techniques.... But, here comes the crux!! We needed some custom implementation for the scheduler, that wasn't being provided by the SSRS scheduler.

So, then I decided to created my own Scheduler that worked on the same architecture of the SQL Server Scheduler.

Prerequisites

In order the run this custom Scheduler, we need the following prerequisites to be present on your system.

1. SQL Server Reporting Services [Reporting server]

2. VS.net [1.1 or 2.0 framework]

3. SMTP server [email client]

4. SQL Server Database [Backend]

Architecture

The Architecture of the system is as per depicted in the diagram:

As per the Architecture, the system[in short] consists of 5 main layers:

1. DAC layer : This is the Data Access Control Layer. It is responsible for accessing the SQL Server Database to retrieve scheduling details.

2. Email Extension layer: This layer is responsible for sending mail using an SMTP emailing server. 

3. Logging Layer: This layer is reponsible for logging error to the user[to be located at D:\ErrorLog.txt] and for Status reporting of the system [D:\hermzLog.txt].  

4. Reporting Extension: This layer is reponsible for obtaining the reports from the report server. The Report name, which is to be fed into the database will be retrieved via this extension.

5. Process Scheduler: It is the heart of the system. Responsible for the main process/thread scheduling. The manner in which the process is scheduled is depicted in the following diagram. 

Instructions

From this article, you will obtain 1. SQL script file and 2. Executable file

Steps:

1. Run the SQL Script using either isqlw or the Enterprise manager[SQL Server 2000]/SQL Server Management Studio[SQL Server 2005].

2. Run the executable at the VS.net command prompt: installutil HermzScheduler.exe

3. Check for presence of Hermz Scheduler in the Services tray. Windows->Run->Services.msc

4. Create a new login for SQL server authentication.

5. Add in Sample data into HermzDB database at Schedule table.

The Occurrence type is 1->Daily, 2-> Weekly, 3-> Monthly.

Report Name -> /SampleProject/SampleReport

From_name, To_name, will be the names to be present in the email

Subject is the subject of the mail.

Pattern - > Null for Daily, DateTime value for Monthly/Weekly

6. Run the HermzScheduler service.

7. If you have Outlook Express installed , then the mail will get automatically populated into you mailbox. Or else, you can view the contents present at location:C:\Inetpub\mailroot\Queue

Conclusion

This tool was created entirely for Hermz Software. Thanks to this, we can create a custom SQL Server Scheduler. I hope you liked this article. Please feel free to mail me incase of any doubts.