Data Driven Subscriptions

Creating a data driven subscription is easy with Boomerang. The example below illustrates how to create a subscription, store the output on a file share and notify report users with an email message.

Create an Event

The unit of work implemented by Boomerang is called Event. The Event is represented by a single record in theEVENT_MASTER table. Before creating the event key (uniqueidentifier), that defines and hold the data driven subscription together, it is need to be declared and set.

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

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 subscription application. EVENT_MASTER.Str1 is used to describe the event. See the EVENT_MASTER page for all available options.

Example:
	Insert EVENT_MASTER (gKey, Source, Created_By, Str1)
	Values (@gKey, 1, 'domain\username', 'My data driven subscription');
Result:
	gKey Source Created_By Str1 ---------/ /----------------- ------- --------------- ---------------------------- 3C2A358C-/ /AD95-80D00F40CB6E 3 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 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.

Create OUT_FILE job
We start by creating our file out job.
Example:
	Insert OUT_FILE (gKey, jKey, Path, Mode)
	Values (@gKey, @jKey_file, '\\unc_path_where_the_report_will_be_saved\', 1);
Result:
	gKey jKey Path Mode ---------/ /------------ ------------------------------------ ------------------ --------- 3C2A358C-/ /80D00F40CB6E 828EE7A8-6CFA-4769-AFBC-A5BE4A0EC442 \\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.

Add Content to the OUT_FILE Job
Next step is to specify what content of the OUT_FILE job. This is done in EVENT_CONTENT and can look like this:
Example:
	Insert EVENT_CONTENT (gKey, jKey, aKey, Src_Type, Path, [Format], Name)
	Values (@gKey, @jKey_file, @aKey_file, 2, 'Misc/Sales_by_Region', 'XLS', 'File_Name');
Result:
	gKey jKey aKey Etc. --------/ /------- ------------/ /------------------ ------------------------------------ 3C2A358C/ /F40CB6E 828EE7A8-6CF/ /-AFBC-A5BE4A0EC442 1FFED18B-E57A-4019-8110-0278633301D2
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:
	Insert CONTENT_PARAMETER (aKey, [Name], [Value])
	Values (@aKey_file, 'Sales_Period', convert(varchar(2), datepart(month, getdate() )));
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.

Create OUT_EMAIL job
This e-mail will notify a user that the OUT_FILE job stored a report ('Misc/Sales_by_Region') on a file server ('\\unc_path_where_the_report_will_be_saved\')
Example:
	Insert OUT_EMAIL (gKey, jKey, IncludeKey, [ReplyTo], [From], Subject, Body)
	Values(@gKey, @jKey_email, 0, 'Datasubsriptions@my_domain', '"e-mail display name" <Datasubsriptions@my_domain>', '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');
Result:
	gKey jKey IncludeKey Etc ------------------------------------ ------------------------------------ ----------- 3C2A358C-C172-479D-AD95-80D00F40CB6E 0621D4FF-91B1-420C-AF82-89B69137BAE5 0

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:
	Insert OUT_EMAIL_RECEPIENT (jKey, Type, Email)
	Values (@jKey_email, 2, 'recepient@domain.com');
Result:
	jKey Type Email ------------------------------------ ----------- ------------------------------- 0621D4FF-91B1-420C-AF82-89B69137BAE5 2 michael@fuel9.com
OUT_EMAIL_RECEPIENT.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_RECEPIENT.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_RECEPIENT.jKey to identify which email to send to which recipients. Of course you can also specify a different message for each recipient in this cae. For all available options see the OUT_EMAIL_RECEPIENT page.

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:
	Update EVENT_MASTER set Status=0 where gKey=@gKey;
Result:
	gKey Status ------------------------------------ ----------- 3C2A358C-C172-479D-AD95-80D00F40CB6E 0

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:
	Update EVENT_STATUS set Run_When = dateadd(hour, 1, getdate()) where jKey=@jKey_email
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

Download Sample1, Sample2

Enjoy.. :)

Translate

Thursday, February 16, 2012