Data Driven Subscriptions in 5 easy steps
Introduction
Creating a data driven subscription is made easy with the Boomerang notification framework. There are many options available, for example you may include the report as a PDF or XLS attachment to an email or add the report in the email body (inline). You may also fax or print the output. However in the example below we illustrates how to create a subscription that stores the output on a file share and notify report users with an email message that the report is ready to be viewed.
This example is based on Boomerang and MS-SQL Server Standard Editions. The SQL Server Express Edition is supported in the current version of Boomerang however there are some limitations of available rending formats in the Express Edition of the SSRS API. To learn more about supported rending formats click here.
1. Create an Event
The unit of work implemented by Boomerang is called Event. The Event is represented by a single record in the EVENT_MASTER table. Before creating the event key (uniqueidentifier), that defines and hold the data driven subscription together, it is needed to be declared and set.
------------- Declare Keys -------------------------------- Declare @gKey uniqueidentifier; set @gKey = newid(); -- Key for my entire event Declare @jKey_file uniqueidentifier; set @jKey_file = newid(); -- Key for my file out job Declare @aKey_file uniqueidentifier; set @aKey_file = newid(); -- Key for my file out report parameters Declare @jKey_email uniqueidentifier; set @jKey_email = newid(); -- Key for my email notification job ------------- Declare Keys -------------------------------- |
The only required columns that do not have a default value are EVENT_MASTER.gKey and EVENT_MASTER.Created_By. We recommend you use these columns to give the event a description and/or classification to make it easier to determine what the event includes and/or is used for. In this example EVENT_MASTER.Source is set to 1 which is a designated integer to group all events bloinging to this particular data driven subscription application. EVENT_MASTER.Str1 is used to describe the event. See the EVENT_MASTER page for all available options.
Example:
---- New Event --------------------------------------- Insert EVENT_MASTER (gKey, Source, Created_By, Str1) Values (@gKey, 1, 'domain\username', 'My data driven subscription') ---- New Event --------------------------------------- |
Result:
gKey Source Created_By Str1 ----------------------------------- ------- --------------- ---------------------------- 8832D40D-4135-4975-8E28-2F8DCA130F1E 1 domain\username My data driven subscription |
Define Jobs to be included in the Event
Each event consists of one or more jobs. A job represents an actual unit of delivery, such as email, fax, or print. Each job declares its own content as well as how and where the job’s output will be delivered. For example, a print job must define the path to a printer. Several jobs can make up a single event. The jobs can be entered in any order but there is no means to control which job will execute first or last.
In this data driven subscription example we will include two jobs in the event: email out and file out represented by the Boomerang tables OUT_EMAIL and OUT_FILE, respectively.
2. Create OUT_FILE job
We start by creating our file out job.
Example:
---- Out File Job --------------------------------------- Insert OUT_FILE (gKey, jKey, Path, Mode) Values (@gKey, @jKey_file, '\\unc_path_where_the_report_will_be_saved\', 1) ---- Out File Job --------------------------------------- |
Result:
gKey jKey Path Mode ------------------------------------ ------------------------------------ ------------------ --------- 8832D40D-4135-4975-8E28-2F8DCA130F1E 623403F8-4AD1-4C10-B28F-2AD91182E138 \\websrv\FS01\tmp\ 1 |
In this example all files created by this job will be saved in the same directory. If your files should be saved in different directories you will need to create an OUT_FILE record for each unique output path. OUT_FILE.Mode equal to 1 (one) specifies that the output file(s) should be created and existing files should be replaced with the new file. See the OUT_FILE page for all available values of Mode and saving files to an FTP server.
3. Add Content to the OUT_FILE Job
Next step is to specify what content of the OUT_FILE job. This is done using the EVENT_CONTENT table and can look like this:
Example:
---- Out File Job Content --------------------------------------- Insert EVENT_CONTENT (gKey, jKey, aKey, Src_Type, Path, [Format], Name) Values (@gKey, @jKey_file, @aKey_file, 2, 'Misc/Sales_YTD_Demo', 'XLS', 'File_Name') ---- Out File Job Content --------------------------------------- |
Result:
gKey jKey aKey Src_Type Path Format Name ------------------------------------ ------------------------------------ ------------------------------------ ------------------------------ ------ --------- 8832D40D-4135-4975-8E28-2F8DCA130F1E 623403F8-4AD1-4C10-B28F-2AD91182E138 1FFED18B-E57A-4019-8110-0278633301D2 2 Misc/Sales_YTD_Demo XLS File_Name |
OUT_FILE.jKey and EVENT_CONTENT.jKey link the job (OUT_FILE in this case) to its content. jKey must be unique for each OUT_FILE record and aKey is unique for each EVENT_CONTENT record. Consequently you must create multiple EVENT_CONTENT records if each OUT_FILE job consists of multiple reports, for example.
EVENT_CONTENT is the common content storage for all types of jobs i.e. OUT_FILE, OUT_PRINT, OUT_EMAIL and OUT_FAX. Therefore there are many columns available in EVENT_CONTENT to define the content for each type of job. In the example above we identified a SQL Server Reporting Server report formatted in Excel with the name “File_Name” as the content of the OUT_FILE job define in the previous step. Below is a list of the columns we used as well as the values you can use for each column.
EVENT_CONTENT.Src_Type = 2 (0 = Streaming, 1 = File, 2 = SQL Reporting Services Report)
EVENT_CONTENT.Format = ‘XLS’ (PDF, HTML, XML etc.)
EVENT_CONTENT.Name =’File_Name’
For a complete description of available options see the EVENT_CONTENT page.
SSRS report parameters are stored in CONTENT_PARAMETER. The exmaple below shows how to define report parameters and set their values. and can look like this
Example:
---- Out File Job Content Parameter --------------------------------------- Insert CONTENT_PARAMETER (aKey, [Name], [Value]) Values (@aKey_file, 'Sales_Period', convert(varchar(2), datepart(month, getdate() ))) ---- Out File Job Content Parameter --------------------------------------- |
Result:
aKey Name [Value] ------------------------------------ ---------------- ------------------- 1FFED18B-E57A-4019-8110-0278633301D2 Sales_Period 3 |
To specify multiple parameters for a single report, use the same value for the akey, the column that links CONTENT_PARAMETER to EVENT_CONTENT. For more details see the CONTENT_PARAMETER page.
4. Create OUT_EMAIL job
This e-mail will notify a user that the OUT_FILE job stored a report (‘Misc/Sales_YTD_Demo’) on a file server (‘\\unc_path_where_the_report_will_be_saved\’)
Example:
---- Out Email Job --------------------------------------- Insert OUT_EMAIL (gKey, jKey, IncludeKey, [Subject], Body) Values (@gKey, @jKey_email, 0, 'Sales by Region', 'A copy of the monthly budget report have now been generated and can be found on the file server in this location: \\ssss\ss') ---- Out Email Job ---------------------------------------; |
Result:
gKey jKey IncludeKey Subject Body ------------------------------------ ------------------------------------ ---------- --------------- -------------------------------------------------------------------------------------------------------------------------- 8832D40D-4135-4975-8E28-2F8DCA130F1E 623403F8-4AD1-4C10-B28F-2AD91182E138 0 Sales by Region A copy of the monthly budget report have now been generated and can be found on the file server in this location: \\ssss\ss |
Setting OUT_EMAIL.IncludeKey to 0 (zero) specifies that advanced tracking of the outgoing email message should be turned off. For all available options see the OUT_EMAIL page. To learn more about different ways of formatting e-mails see the Email Formatting page.
Last step before releasing the event in EVENT_MASTER for processing by the Boomerang services is to add one or more recipient to the notification created in the previous step.
Example:
---- Out Email Job Recipient --------------------------------------- Insert OUT_EMAIL_RECIPIENT (jKey, Type, Email) Values (@jKey_email, 2, 'recipient@domain.com') ---- Out Email Job Recipient --------------------------------------- |
Result:
jKey Type Email ------------------------------------ ----------- ------------------------------- 623403F8-4AD1-4C10-B28F-2AD91182E138 2 info@fuel9.com |
OUT_EMAIL_RECIPIENT.Type set to 2 (two) specifies that the recipient should be in the Cc section of the email. If recipients of the email share the OUT_EMAIL_RECIPIENT.jKey they will all get the same email and all email addresses will be visible to all recipients. To hide email addresses set Type to 3 (Bcc). To create individual emails insert unique keys for each OUT_EMAIL.jKey and OUT_EMAIL_RECIPIENT.jKey to identify which email to send to which recipients. Of course you can also specify a different message for each recipient in this case. For all available options see the OUT_EMAIL_RECIPIENT page.
5. Release the Event
Last step is to release the event to be processed. In this case Boomerang will send an email and store a SQL reporting server report to a directory i.e. the two jobs OUT_EMAIL and OUT_FILE specified above. To release the event simply set the status to 0 as shown below:
Example:
---- Release Event --------------------------------------- Update EVENT_MASTER set Status=0 where gKey=@gKey ---- Release Event --------------------------------------- |
Result:
gKey Status ------------------------------------ ----------- 8832D40D-4135-4975-8E28-2F8DCA130F1E 0 |
Entire Transaction
The whole data driven subscription transaction put together would look something like this:
------------- Declare Keys -------------------------------- Declare @gKey uniqueidentifier; set @gKey = newid(); -- Key for my entire event Declare @jKey_file uniqueidentifier; set @jKey_file = newid(); -- Key for my file out job Declare @aKey_file uniqueidentifier; set @aKey_file = newid(); -- Key for my file out report paramaters Declare @jKey_email uniqueidentifier; set @jKey_email = newid(); -- Key for my email notification job ---- New Event --------------------------------------- Insert EVENT_MASTER (gKey, Source, Created_By, Str1) Values (@gKey, 1, 'domain\username', 'My data driven subscription'); ---- Out File Job --------------------------------------- Insert OUT_FILE (gKey, jKey, Path, Mode) Values (@gKey, @jKey_file, '\\unc_path_where_the_report_will_be_saved\', 1); ---- Out File Job Content --------------------------------------- Insert EVENT_CONTENT (gKey, jKey, aKey, Src_Type, Path, [Format], Name) Values (@gKey, @jKey_file, @aKey_file, 2, 'Misc/Sales_YTD_Demo', 'XLS', 'File_Name') ---- Out File Job Content Parameter --------------------------------------- Insert CONTENT_PARAMETER (aKey,[Name],[Value]) Values (@aKey_file,'Sales_Period', convert(varchar(2), datepart(month, getdate()))) ---- Out Email Job --------------------------------------- Insert OUT_EMAIL (gKey, jKey, IncludeKey, [Subject], Body) Values(@gKey, @jKey_email, 0, 'Sales by Region', 'A copy of the monthly budget report have now been generated and can be found on the file server in this location: \\ssss\ss'); ---- Out Email Job Recipient --------------------------------------- Insert OUT_EMAIL_RECIPIENT (jKey, Type, Email) values (@jKey_email, 2, 'recipient@domain.com') ---- Release Event --------------------------------------- Update EVENT_MASTER set Status=0 where gKey=@gKey; |
Email Notification
The Email Notification for our data driven subscriptions looks like this:
Excel File Out
The Excel file out our data driven subscriptions looks like this:
Delay and Sequencing Jobs
Although several jobs make up a single event, no specific order is guaranteed in which the jobs will fire. Because of the multi-threaded nature of Boomerang services, chances are they will fire all at once as soon as the corresponding event record is marked “ready” (EVENT_MASTER.Status = 0). To address this two methods are available; EVENT_STATUS.Run_When and Boomerang.dbo.sp_After_XXXX_Out.
The EVENT_STATUS is inserted and updated by the Boomerang services and shows the current status of all events. The EVENT_STATUS record is created when a job is inserted into OUT_EMAIL, OUT_FAX, OUT_PRINT or OUT_FILE. However it may be manipulated to delay (defer) any of the jobs within an event. To delay an email notification and give the OUT_FILE job time to process you can set the value of Run_When in EVENT_STATUS. The example below delays the email notification by 1 hour.
Example:
---- delay sending by 1 hour --------------- Update EVENT_STATUS set Run_When= dateadd(hour, 1, getdate()) where jKey=@jKey_email ---- release jobs -------------------------- update EVENT_MASTER set Status = 0 where gKey = @gKey |
The second option is to use the event handlers. The event handlers are invoked by the Boomerang services when a job finishes, regardless of success or failure. There is a different event handler for each job type. To ensure that the OUT_FILE job in the above example completed successfully before sending a notification Boomerang.dbo.sp_After_File_Out may be used.
Example:
Note: The event handler procedures should not be called directly by the developer. Any changes to handler parameter list cannot be made. New parameters may be added with future Boomerang releases upon which a notice will be sent separately.
Alter procedure [dbo].[sp_After_File_Job] ( @jKey uniqueidentifier , @lKey uniqueidentifier , @error_level int ) as begin set nocount on /* This section will handle all notifications for any file out job that failed. We're passing the lKey to the store proc so that error messages can easily be retrieved from EVENT_LOG */ if @error_level != 0 begin exec Custom_sp_Send_Admin_Email_Notification @lKey = @lKey end /* If the job is successful we look up what kind of file out job so that we can send different notifications for different types of jobs */ if @error_level = 0 begin declare @Type_Of_Notification int -- Here we're using Source in EVENT_MASTER do distinguishing between different file out jobs select @Type_Of_Notification = Source from dbo.EVENT_MASTER where gKey=(select gKey from dbo.OUT_FILE where jKey=@jKey) -- Send notification for successful file out job exec Custom_sp_Send_File_Out_Notification @Type_Of_Notification = @Type_Of_Notification, @Processed_jKey=@jKey end end |
To learn more about event handlers and helpers got to this section: Helpers
Conclusion
Boomerang takes away the chore of writing complex code to interface with notification infrastructure like twitter, e-mail, printers, file and fax servers. By means of the Boomerang framework IT Professionals can create robust and dynamic notification solution with a minimum amount of T-SQL code. As demonstrated above we created a tailored data driven subscription using SSRS reports and a few lines of t-sql.
Download Sample
Download above data driven subscriptions example here
