Creating Data-driven Subscription Programmatically using C# – Delivery Extension : Email

In my last post I have mentioned I will write a post about creating Data-driven subscription programmatically and this is it. I am creating this subscription on Microsoft SQL Server 2008R2 using the method   CreateDataDrivenSubscription in ReportService2010 web service. In one of my last posts I wrote about Accessing Report Server using Report Server Web Service.

Reporting Services includes an e-mail delivery extension and a file share delivery extension. In here I will be using Email  Delivery Extension.

Before creating the Data-driven subscription make sure SQL Server Agent service is up and running. That’s because what really happens when you create Data-driven subscription is, there will be job created in SQL Server.

SQL Server Agent

Without much information, I am pasting the code down here with all the comments. I have added a service reference to ReportService2010 web service.

using System;
using System.Globalization;
using System.Net;
using System.Security.Principal;
using System.Web.Services.Protocols;
using System.Windows.Forms;
using DataDrivenSubscriptionWebApp.wsReportService;

namespace DataDrivenSubscriptionWebApp
{
    public partial class _Default : System.Web.UI.Page
    {
        protected void Page_Load(object sender, EventArgs e)
        {

        }

        protected void Button1_Click(object sender, EventArgs e)
        {
            CreateDataDrivenSubscription();
        }

        private static void CreateDataDrivenSubscription()
        {
            // connecting to report server web service
            var clientCredentials = new NetworkCredential("administrator", "123@com");
            var rs = new ReportingService2010SoapClient();
            if (rs.ClientCredentials != null)
            {
                rs.ClientCredentials.Windows.AllowedImpersonationLevel = TokenImpersonationLevel.Impersonation;
                rs.ClientCredentials.Windows.ClientCredential = clientCredentials;
            }
            rs.Open();

            var oTrustedUserHeader = new TrustedUserHeader();

            // report which i am creating data-driven subscription for
            const string report = "/My Reports/Reports/Report1";
            const string description = "Programmatic Data Driven Subscription for Email";

            // Set the extension setting as report server email.
            var settings = new ExtensionSettings {Extension = "Report Server Email"};

            // Set the extension parameter values.
            var extensionParams = new ParameterValueOrFieldReference[8];

            var to = new ParameterFieldReference {ParameterName = "TO", FieldAlias = "EMAIL"}; // Data-driven.
            extensionParams[0] = to;

            var replyTo = new ParameterValue {Name = "ReplyTo", Value = "jaliya.udagedara@gmail.com"};
            extensionParams[1] = replyTo;

            var includeReport = new ParameterValue {Name = "IncludeReport", Value = "False"};
            extensionParams[2] = includeReport;

            var renderFormat = new ParameterValue {Name = "RenderFormat", Value = "HTML4.0"};
            extensionParams[3] = renderFormat;

            var priority = new ParameterValue {Name = "Priority", Value = "NORMAL"};
            extensionParams[4] = priority;

            var subject = new ParameterValue {Name = "Subject", Value = "Subsribed Report"};
            extensionParams[5] = subject;

            var comment = new ParameterValue {Name = "Comment", Value = "Here is the link to your report."};
            extensionParams[6] = comment;

            var includeLink = new ParameterValue {Name = "IncludeLink", Value = "True"};
            extensionParams[7] = includeLink;

            settings.ParameterValues = extensionParams;

            // Create the data source for the delivery query.
            var delivery = new DataSource {Name = ""};
            var dataSourceDefinition = new DataSourceDefinition
                                           {
                                               ConnectString = "Data Source=(local);Initial Catalog=SAMPLE_DB",
                                               CredentialRetrieval = CredentialRetrievalEnum.Store,
                                               Enabled = true,
                                               EnabledSpecified = true,
                                               Extension = "SQL",
                                               ImpersonateUserSpecified = false,
                                               UserName = "sa",
                                               Password = "sa"
                                           };
            delivery.Item = dataSourceDefinition;

            // Create the data set for the delivery query.
            var dataSetDefinition = new DataSetDefinition
                                        {
                                            AccentSensitivitySpecified = false,
                                            CaseSensitivitySpecified = false,
                                            KanatypeSensitivitySpecified = false,
                                            WidthSensitivitySpecified = false
                                        };

            var queryDefinition = new QueryDefinition
                                      {
                                          CommandText = "SELECT EMAIL from RECIPIENT",
                                          CommandType = "Text",
                                          Timeout = 45,
                                          TimeoutSpecified = true
                                      };
            dataSetDefinition.Query = queryDefinition;
            var results = new DataSetDefinition();
            var oServerInfoHeader = new ServerInfoHeader();

            bool changed;
            string[] paramNames;
            oServerInfoHeader = rs.PrepareQuery(oTrustedUserHeader, delivery, dataSetDefinition, out results, out changed,
                                                out paramNames);

            var dataRetrieval = new DataRetrievalPlan {DataSet = results, Item = dataSourceDefinition};

            // Set the event type and match data for the delivery.
            const string eventType = "TimedSubscription";
            const string matchData = "<ScheduleDefinition><StartDateTime>2012-10-01T14:00:00-07:00</StartDateTime><WeeklyRecurrence><WeeksInterval>1</WeeksInterval><DaysOfWeek><Monday>True</Monday><Tuesday>True</Tuesday><Wednesday>True</Wednesday><Thursday>True</Thursday><Friday>True</Friday></DaysOfWeek></WeeklyRecurrence></ScheduleDefinition>";

            //const string eventType = "SnapshotUpdated";
            //const string matchData = null;

            // Set the report parameter values.
            var parameters = new ParameterValueOrFieldReference[1];

            // i am retrieving value EMAIL from database and I am passing that value as my report parameter value
            var reportparam = new ParameterFieldReference {ParameterName = "ReportParameter1", FieldAlias = "EMAIL"}; // Data-driven.
            
            parameters[0] = reportparam;

            try
            {
                string subscriptionId = "";
                oServerInfoHeader = rs.CreateDataDrivenSubscription(oTrustedUserHeader, report, settings, dataRetrieval,
                                                                    description, eventType, matchData, parameters,
                                                                    out subscriptionId);
            }
            catch (SoapException ex)
            {
                MessageBox.Show(ex.Detail.InnerText.ToString(CultureInfo.InvariantCulture));
            }
            rs.Close();
        }
    }
}

Once you run this, you can see that there is Data-driven subscription created under your report.

Manage Report

Subscription

And you can also see a job created under SQL Server Agent in SQL Server Management Studio.

SQL Server Agent

You can manually start a job in SQL Server Agent by running following command in Management Studio query window.

USE msdb 
EXEC sp_start_job @job_name = '025B869B-BA52-4337-BC8B-DC65383EAC7C' --your job id

Hope this helps.

Happy Coding.

Regards,
Jaliya