Translate

Thursday, February 16, 2012

Data Driven Subscriptions

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 uniqueidentifierset @gKey = newid(); -- Key for my entire event

Declare @jKey_file uniqueidentifierset @jKey_file = newid()-- Key for my file out job
Declare @aKey_file uniqueidentifierset @aKey_file = newid()-- Key for my file out report parameters

Declare @jKey_email uniqueidentifierset @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.. :)

No comments:

Post a Comment