Data driven reports with dynamic delivery via SSIS, Reporting Services and SQL 2005

I was recently asked by a client to develop an automated report capability that would go out to account holders monthly or quarterly with current balances of in house credit and use etc. The report creation itself would be no problem. Creating a data driven list of clients too would be a simple matter. I was however stumped when it came to how to parameterize each report and deliver it via R.S. methods to the appropriate account. Boy I would be just pleased as punch if I could leverage the R.S. subscription service to handle this request! With thousands of clients, the concept of timed report subscriptions for each did not even cross my mind. However I got to doing some poking around and found a solution that could possibly be of interest to those who needed to create dynamic paramterized reports and automatically sent to distinct email addresses.

I will start this posting with some SQL code I cam across,the author has put his John Hancock in the comments,..

USE [ReportServer]
GO
/****** Object: StoredProcedure [dbo].[data_driven_subscription] Script Date: 07/23/2008 21:34:50 ******/
SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER OFF
GO
--------------------------------------------------------------------------------
/*
DATE CREATED: 12/21/2006
AUTHOR: Jason L. Selburg
PURPOSE:
This procedure extends the functionality of the subscription feature in
Microsoft SQL Reporting Services 2005, allowing the subscriptions to be triggered
via code.
The code supplied will function with reports that have one parameter. Reports
that have multiple parameters must be addressed individually or with another method.
There are many possible ways to handle multi-parameter reports, which is why it is not addressed here.
However, one suggestion:
- Create a subscription table that will hold subscription names and IDs.
- Create a table to hold subscription IDs mapped to the previous table and hold the parameter names and
values.
- These tables would be queried and looped through to populate the parameter XML string below.
NOTES:
This procedure does not address "File Server Share" subscriptions.
PARAMETERS:
@scheduleName = This is the NAME that is put into the subject line of the subscription when created.
It is STRONGLY suggested that you use a naming convention that will prevent
duplicate names.
@emailTO = The TO of the email (not required.) \
@emailCC = The CC of the email (not required.) ---One of these are REQUIRED!
@emailBCC = The BCC of the email (not required.) /
@emailReplyTO = The reply to address that will appear in the email.
@emailBODY = The text in the body of the email.
@parameterName = The paramerter name. This MUST match the parameter name in the report definition.
@parameterValue = The parameter value.
@sub = The subject line of the email.
@renderFormat = The rendering format of the report.
VALID VALUES : May be different depending on the installation and configuration
of your server, but these are listed in the "reportServer.config" file.
This file is located in a folder similar to
"C:\Program Files\Microsoft SQL Server\MSSQL.2\Reporting Services\ReportServer\"
XML
IMAGE
PDF
EXCEL
CSV
@exitCode = The returned integer value of the procedure's execution result.
-1 'A recipient is required.'
-2 'The subscription does not exist.'
-3 'No delivery settings were supplied.'
-4 'A data base error occurred inserting the subscription history record.'
-5 'A data base error occurred clearing the previous subscription settings.'
-6 'A data base error occurred retrieving the TEXT Pointer of the Delivery Values.'
-7 'A data base error occurred updating the Delivery settings.'
-8 'A data base error occurred retrieving the TEXT Pointer of the Parameter Values.'
-9 'A data base error occurred updating the Parameter settings.'
-10 'A data base error occurred updating the subscription history record.'
-11 'A data base error occurred resetting the previous subscription settings.'
@exitMessage = The text description of the failure or success of the procedure.
PRECONDITIONS:
The subscription being called must exist and the SUBJECT line of the subscription MUST contain
the exact name that is passed into this procedure.
If any of the recipients email address are outside of the report server's domain, then you may
need to contact your Network Administrator to allow email forwarding from your email server.
POST CONDITIONS:
The report is delivered or an error code and message is returned.
SECURITY REQUIREMENTS:
The user which calls this stored procedure must have execute permissions.
DEPENDANCES:
Tables:
ReportSchedule = Installed with SQL RS 2005
Subscription_History = Must be created using the following script.
---------------------------------------------------------------------
CREATE TABLE [dbo].[Subscription_History](
[nDex] [int] IDENTITY(1,1) NOT NULL,
[SubscriptionID] [uniqueidentifier] NULL,
[ScheduleName] [nvarchar](260) COLLATE Latin1_General_CI_AS_KS_WS NULL,
[parameterSettings] [varchar](8000) COLLATE Latin1_General_CI_AS_KS_WS NULL,
[deliverySettings] [varchar](8000) COLLATE Latin1_General_CI_AS_KS_WS NULL,
[dateExecuted] [datetime] NULL,
[executeStatus] [nvarchar] (260) NULL,
[dateCompleted] [datetime] NULL,
[executionTime] AS (datediff(second,[datecompleted],[dateexecuted])),
CONSTRAINT [PK_Subscription_History] PRIMARY KEY CLUSTERED
(
[nDex] ASC
)WITH (PAD_INDEX = OFF, IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
---------------------------------------------------------------------
Subscriptions = Installed with SQL RS 2005
Schedule = Installed with SQL RS 2005
*/
CREATE procedure [dbo].[data_driven_subscription]
( @scheduleName nvarchar(255),
@emailTO nvarchar (2000) = NULL,
@emailCC nvarchar (2000) = NULL,
@emailBCC nvarchar (2000) = NULL,
@emailReplyTO nvarchar (2000) = NULL,
@emailBODY nvarchar (4000) = NULL,
@parameterName nvarchar(4000) = NULL,
@parameterValue nvarchar (256) = NULL,
@sub nvarchar(1000) = NULL,
@renderFormat nvarchar(50) = 'PDF',
@exitCode int output,
@exitMessage nvarchar(255) output
)
AS
DECLARE
@ptrval binary(16),
@PARAMptrval binary(16),
@subscriptionID uniqueidentifier,
@scheduleID uniqueidentifier,
@starttime datetime,
@lastruntime datetime,
@execTime datetime,
@dVALUES nvarchar (4000),
@pVALUES nvarchar (4000),
@previousDVALUES nvarchar (4000),
@previousPVALUES nvarchar (4000),
@lerror int,
@insertID int,
@lretval int,
@rowcount int
SET @starttime = DATEADD(second, -2, getdate())
SET @emailTO = rtrim(IsNull(@emailTO, ''))
SET @emailCC = rtrim(IsNull(@emailCC, ''))
SET @emailBCC = rtrim(IsNull(@emailBCC, ''))
SET @emailReplyTO = rtrim(IsNull(@emailReplyTO, ''))
SET @emailBODY = rtrim(IsNull(@emailBODY, ''))
SET @parameterValue = rtrim(IsNull(@parameterValue, ''))
SET @lerror = 0
SET @rowcount = 0
IF @emailTO = '' AND @emailCC = ''
AND @emailBCC = ''
BEGIN
SET @exitCode = -1
SET @exitMessage = 'A recipient is required.'
RETURN 0
END

-- get the subscription ID
SELECT
@subscriptionID = rs.subscriptionID,
@scheduleID = rs.ScheduleID
FROM
ReportSchedule rs
INNER JOIN subscriptions s
ON rs.subscriptionID = s.subscriptionID
WHERE
extensionSettings like '%' + @scheduleName + '%'
IF @subscriptionID Is Null
BEGIN
SET @exitCode = -2
SET @exitMessage = 'The subscription does not exist.'
RETURN 0
END
/* just to be safe */
SET @dVALUES = ''
SET @pVALUES = ''
SET @previousDVALUES = ''
SET @previousPVALUES = ''
/* apply the settings that are defined */
IF IsNull(@emailTO, '') <> ''
SET @dVALUES = @dVALUES + 'TO'
+ @emailTO + ''
IF IsNull(@emailCC, '') <> ''
SET @dVALUES = @dVALUES + 'CC'
+ @emailCC + ''
IF IsNull(@emailBCC, '') <> ''
SET @dVALUES = @dVALUES + 'BCC'
+ @emailBCC + ''
IF IsNull(@emailReplyTO, '') <> ''
SET @dVALUES = @dVALUES + 'ReplyTo'
+ @emailReplyTO + ''
IF IsNull(@emailBODY, '') <> ''
SET @dVALUES = @dVALUES + 'Comment'
+ @emailBODY + ''
IF IsNull(@sub, '') <> ''
SET @dVALUES = @dVALUES + 'Subject'
+ @sub + ''
IF IsNull(@dVALUES , '') <> ''
SET @dVALUES = '' + @dVALUES
+ 'IncludeReportTrue'
IF IsNull(@dVALUES , '') <> ''
SET @dVALUES = @dVALUES + 'RenderFormat' +
@renderFormat + '' +
'IncludeLinkFalse'
IF IsNull(@parameterName, '') <> '' and IsNull(@parameterValue, '') <> ''
SET @pVALUES = '' +
@parameterName +
'' +
@parameterValue +
''
/* verify that some delivery settings where passed in */
-- @pVALUES are not checked as they may all be defaults
IF IsNull(@dVALUES , '') = ''
BEGIN
SET @exitCode = -3
SET @exitMessage = 'No delivery settings were supplied.'
RETURN 0
END
/* get the current parameter values and delivery settings */
SELECT @previousDVALUES = extensionSettings
FROM Subscriptions
WHERE SubscriptionID = @SubscriptionID
SELECT @previousPVALUES = parameters
FROM Subscriptions
WHERE SubscriptionID = @SubscriptionID
UPDATE Subscriptions
SET extensionSettings = '', parameters = ''
WHERE SubscriptionID = @SubscriptionID
SELECT @lerror=@@error, @rowCount=@@rowCount
IF @lerror <> 0 OR IsNull(@rowCount, 0) = 0
BEGIN
SET @exitcode = -5
SET @exitMessage = 'A data base error occurred clearing the previous subscription settings.'
RETURN IsNull(@lerror, 0)
END
-- set the text point for this record
SELECT @ptrval = TEXTPTR(ExtensionSettings)
FROM Subscriptions
WHERE SubscriptionID = @SubscriptionID
SELECT @lerror=@@error
IF @lerror <> 0 OR @ptrval Is NULL
BEGIN
SET @exitcode = -6
SET @exitMessage = 'A data base error occurred retrieving the TEXT Pointer of the Delivery Values.'
RETURN IsNull(@lerror, 0)
END
UPDATETEXT Subscriptions.ExtensionSettings
@ptrval
null
null
@dVALUES
SELECT @lerror=@@error
IF @lerror <> 0
BEGIN
SET @exitcode = -7
SET @exitMessage = 'A data base error occurred updating the Delivery settings.'
RETURN IsNull(@lerror, 0)
END
-- set the text point for this record
SELECT @PARAMptrval = TEXTPTR(Parameters)
FROM Subscriptions
WHERE SubscriptionID = @SubscriptionID
SELECT @lerror=@@error
IF @lerror <> 0 OR @ptrval Is NULL
BEGIN
SET @exitcode = -8
SET @exitMessage = 'A data base error occurred retrieving the TEXT Pointer of the Parameter Values.'
RETURN IsNull(@lerror, 0)
END
UPDATETEXT Subscriptions.Parameters
@PARAMptrval
null
null
@pVALUES
SELECT @lerror=@@error
IF @lerror <> 0
BEGIN
SET @exitcode = -9
SET @exitMessage = 'A data base error occurred updating the Parameter settings.'
RETURN IsNull(@lerror, 0)
END
/* insert a record into the history table */
SET @execTime = getdate()
INSERT Subscription_History
(subscriptionID, scheduleName, ParameterSettings, DeliverySettings, dateExecuted, executeStatus)
VALUES
(@subscriptionID, @scheduleName, @parameterValue, @dVALUES , @execTime, 'incomplete' )
SELECT @lerror=@@error, @insertID=@@identity
IF @lerror <> 0 OR IsNull(@insertID, 0) = 0
BEGIN
SET @exitcode = -4
SET @exitMessage = 'A data base error occurred inserting the subscription history record.'
RETURN IsNull(@lerror, 0)
END
-- run the job
EXEC msdb..sp_start_job @job_name = @scheduleID
-- this gives the report server time to execute the job
SELECT @lastruntime = LastRunTime FROM ReportServer..Schedule WHERE ScheduleID = @scheduleID
WHILE (@starttime > @lastruntime)
BEGIN
WAITFOR DELAY '00:00:01'
SELECT @lastruntime = LastRunTime FROM ReportServer..Schedule WHERE ScheduleID = @scheduleID
END
/* update the history table with the completion time */
UPDATE Subscription_History
SET dateCompleted = getdate()
WHERE subscriptionID = @subscriptionID
and scheduleName = @scheduleName
and ParameterSettings = @parameterValue
and dateExecuted = @execTime
SELECT @lerror=@@error, @rowCount=@@rowCount
IF @lerror <> 0 OR IsNull(@rowCount, 0) = 0
BEGIN
SET @exitcode = -10
SET @exitMessage = 'A data base error occurred updating the subscription history record.'
RETURN IsNull(@lerror, 0)
END
/* reset the previous delivery and parameter values */
UPDATE Subscriptions
SET extensionSettings = @previousDVALUES
, parameters = @previousPVALUES
WHERE SubscriptionID = @SubscriptionID
SELECT @lerror=@@error, @rowCount=@@rowCount
IF @lerror <> 0 OR IsNull(@rowCount, 0) = 0
BEGIN
SET @exitcode = -11
SET @exitMessage = 'A data base error occurred resetting the previous subscription settings.'
RETURN IsNull(@lerror, 0)
END
/* return the result of the subscription */
SELECT @exitMessage = LastStatus
FROM subscriptions
WHERE subscriptionID = @subscriptionID
SET @exitCode = 1
RETURN 0

The above code is ran against the ReportServer db instance. Follow his directions on setting up a 'valid' subscription which will never ever run (the end date is somewhere in history) Calling the simple data_driven_subscription proc will fire off the report, and send it to the recipient in the format you are requesting. To read more about this code check it out here It is a real gem and big ups to Jason Selburg for figuring this out, huge stud!


After working with the data_driven_subscription proc, I found that there were a couple of gotchas. One is that even if your report requires no parameter at all, you still need to create a 'parameter' for the report. You have to set the "@parameterName" param for the data_driven_subscription procedure or else you will get a "Root Element is Missing" error in report manager when you view the status of the subscription. Also the 'parameter' for your RS report has to be a VARCHAR datatype. That is a slight limitation (I will post another concept I have used in order to use multiple parameters and of differing data types)

So now it is within grasp, If I can dynamically generate a list of Account IDs and according to the Account table who to send the email, we pass those parameters to the data_driven_subscription stored proc and presto change-o the report would be kicked off and with the directive to send the report (@EmailTO) in the output format we specify (@RenderFormat).


Now we must roll up the sleeves for the SSIS process. I took a three day course on SSIS (MCDBA 2792A) and the instructor was adamant that SSIS barely made it to the dance floor for 2005. This seems true, it is a little quirky but it does the trick. I will include some images...

Do not forget to first set up your connection managers etc.

Next set up your variables. We will need them to work at the package level, and you will have to create a ResultSet variable with a datatype of Object. In this shot I have also an email and ID variable.





Next up is an Execute SQL task on the control flow surface. I love the simplicity of the SQL task. The query is any basic statement, there are just a couple of settings to make sure our result set will be available later. In properties set the ResultSet to "Full Result Set" Next Select "Result Set" from the left hand panel of the "Execute SQL Task Editor" set the result set name to "0" and select the variable from the drop down list. Select our Object variable. In this instance it is "User::CAMResultSet" Also be sure that any parameter data (in this case an ID) you are pulling in is converted to VARCHAR if possible in your statement. You will see why that is necessary later.


On success we will head to a ForEach loop. This will loop over our dataset. The properties necessary to acomplish this are as follows Collection: Enumerator = Foreach ADO Enumerator, ADO object source variable = User::CAMResultSet, EnumerationMode=Rows in first table.

Variable Mappings: The index will depend on the order of your select statement in the preceding execute SQL task (zero based) in our case it will look something like this

Variable: User::CAMID Index:0
Variable: User::CAMEmail Index:1

So now it will loop over each recors and set the package variables. Next the Magic occurs in the "Generate Subscription" execute sql task. It is using a connection to our ReportServer db instance.

A couple settings before we go on the "Generate Subscription" execute sql task.

General: No result set necessary, connection to report server, our wonderful sql statement within to generate the Data Driven Subscription, like so

EXEC [data_driven_subscription]
@scheduleName = '(YourSubscriptionSubjectName)',
@emailTo = ?,
@emailCC = '',
@emailBCC = '',
@emailReplyto='reports@(yourdomain)',
@emailBody='',
@ParameterName='(YourParameterName)',
@ParameterValue=? ,
@sub='(RunTimeEmailSubject)',
@renderFormat = 'MHTML',
@ExitCode =0,
@exitMessage=''

(yes subscription Subject name, take a look at the comments in the data_drive_subscription proc)

Notice the two "?" marks, those represent our variables in index order. The first maps to the field "CAMEmail". The second to CAMID, and is being passed in as the parameter for the report (converted to VARCHAR in our original SELECT statement) The report is expecting the param, and utilizes it to render the report.

Other properties for the "Generate Subscription" include

Parameter Mapping:
Variable Name Direction Data Type Parameter Name
User:CAMEmail Input VARCHAR 0
User::CAMID Input VARCHAR 1

That is about the down and dirty of it. When this package runs, it will get an output of EMails and ID's, shred through them with the ForEach loop, and use them in Jason Selburg's data_driven_subscription proc. No need to handle the web service, parse a web response, or mess with SMTP connections and programming if it is already set up. The reporting services report is waiting for that CAM id and will base the report on it, making the report individualized for the recipient.

I'm no incredible programming mastermind, this post proves it. I am merely taking a few eggs an attempting a quiche. If something is unclear or needs more explanation let me know, I will update as necessary or try and answer your questions if possible.

However you may be asking, what if my report needs more than one parameter? What if I need reports to run dynamically as events occur, not just scheduled,..?? Well stay tuned for another post where I will show how I have extended this concept into a fully automated report platform that will be able to send rich dynamic reports with the ease of inserting a single record!