Programmatically Create Data Driven Subscriptions in SQL Server 2005/2008

In this post, we will see how to programmatically create Data Driven Subscriptions in SQL Server 2005 or SQL Server 2008.

Subscription for Windows File Share

Prerequisites: We need to create a shared folder which has write access for the account which we will provide, when the subscription is being created. Make sure that SQL Server Agent is running before the subscription is created.

  1. Run following command from Visual Studio Command prompt to create .cs file named ReportingService2005.cs
    wsdl /language:CS /n:"Microsoft.SqlServer.ReportingServices2005" http://<Server Name>/reportserver/reportservice2005.asmx?wsdl
    This is applicable for SQL Server 2005 as well as SQL Server 2008.
  2. For the newly created .cs, we need to create assembly (dll) named ReportingService2005.dll either by using csc utility from command prompt or by creating a new class library and building dll. We need to set the reference to System.Web.Services as some methods from the assembly are used in the code.
  3. We can create an executable which will create Data Driven Subscription. The code creates DDS for sample report named ‘Sales Order Detail ‘which is provided with ‘Adventure works’ sample reports. This report takes a parameter for Sales Order Id which we need to provide.
  4. We can create another table which will provide the list of Sales Order numbers for which the reports will be generated with the help of subscription and delivered in the shared folder.

The code to be used is given below:

//create a new instance of the class created previously
ReportingService2005 rs = new ReportingService2005();
rs.Credentials = System.Net.CredentialCache.DefaultCredentials;

//the name of the report for which the DDS is to be created
string report = "/Sales Order Detail";
string description = "Programmatic Data Driven Subscription for Windows File Share";

//set extension as Windows File Share
ExtensionSettings settings = new ExtensionSettings();
settings.Extension = "Report Server FileShare";

//set extension parameters values
//we need to set the values for the different names for files,
//path where the files will be delivered, the rendering format
//for the report, the user name and
//password which has write access to shared folder
ParameterValueOrFieldReference[] extensionParams =
new ParameterValueOrFieldReference[5];

//this parameter takes the name for the file created with report same as the
//sales order id.
ParameterFieldReference fnm = new ParameterFieldReference();
fnm.ParameterName = "FILENAME";
fnm.FieldAlias = "salesorderid";
extensionParams[0] = fnm;

//this path should follow UNC standard
ParameterValue Path = new ParameterValue();
Path.Name = "PATH";
Path.Value = @"\\<server name>\<shared folder name>";
extensionParams[1] = Path;

ParameterValue renderFormat = new ParameterValue();
renderFormat.Name = "RENDER_FORMAT";
renderFormat.Value = "HTML4.0";
extensionParams[2] = renderFormat;

ParameterValue uname = new ParameterValue();
uname.Name = "USERNAME";
uname.Value = "<user name>";
extensionParams[3] = uname;

ParameterValue password = new ParameterValue();
password.Name = "PASSWORD";
password.Value = "<password>";
extensionParams[4] = password;

//this step will assign all the parameter values
settings.ParameterValues = extensionParams;

//create data source
//I have created another table named OrdersForDDS which has only one field
//SalesOrderId for which different reports need to be created in the same
//database adventureworks
DataSource delivery = new DataSource();
delivery.Name = "";
DataSourceDefinition dataSourceDef = new DataSourceDefinition();
dataSourceDef.ConnectString = "data source=<instance name>; " +
"initial catalog=adventureworks;integrated security=true";
dataSourceDef.CredentialRetrieval = CredentialRetrievalEnum.Store;
dataSourceDef.Enabled = true;
dataSourceDef.EnabledSpecified = true;

dataSourceDef.Extension = "SQL";
dataSourceDef.ImpersonateUserSpecified = false;
dataSourceDef.UserName = "<user Name";
dataSourceDef.Password = "<password>";
delivery.Item = dataSourceDef;

//we will have to declare the list of parameter(s) the report requires
//in this report there is only one parameter named “Sales Order Number”
//create fields list which is comprising of only one field in this report

Field[] fieldlist = new Field[1];
fieldlist[0] = new Field();
fieldlist[0].Name = "SalesOrderNumber";
fieldlist[0].Alias = "salesorderid";


//create data set
DataSetDefinition dataSetDefinition = new DataSetDefinition();
dataSetDefinition.AccentSensitivitySpecified = false;
dataSetDefinition.CaseSensitivitySpecified = false;
dataSetDefinition.KanatypeSensitivitySpecified = false;
dataSetDefinition.WidthSensitivitySpecified = false;
dataSetDefinition.Fields = fieldlist;
QueryDefinition queryDefition = new QueryDefinition();
queryDefition.CommandText = "select salesorderid from Sales.OrderForDDS”;
queryDefition.CommandType = "Text";
queryDefition.Timeout = 30;
queryDefition.TimeoutSpecified = true;
dataSetDefinition.Query = queryDefition;
DataSetDefinition results = new DataSetDefinition();
bool changed;
string[] paramNames;
try
{
results = rs.PrepareQuery(delivery, dataSetDefinition, out changed, out paramNames);
}
catch (SoapException ex)
{
MessageBox.Show(ex.Detail.InnerText.ToString());

}
DataRetrievalPlan dataRetrieval = new DataRetrievalPlan();
dataRetrieval.DataSet = results;
dataRetrieval.Item = dataSourceDef;

//set event type and match data
//with matchdata we need to specify details like schedule for the subscription
string EventType = "TimedSubscription";
string matchData="<ScheduleDefinition xmlns:xsd='http://www.w3.org/2001/XMLSchema'" +
"xmlns:xsi='http://www.w3.org/2001/XMLSchema-instance'> <StartDateTime xmlns=" +
"'http://schemas.microsoft.com/sqlserver/2006/03/15/reporting/reportingservices'>" +
"2009-07-28T09:30:00.000+05:30</StartDateTime></ScheduleDefinition>";

//set report parameter value
//the parameter value is based on the field salesorderid created in the field list
//thus the parameter will have as many values as the number of records in the
//table OrderForDDS and those many reports will be delivered to the windows file
//share
ParameterValueOrFieldReference[] parameters = new
ParameterValueOrFieldReference[1];
ParameterFieldReference orderid = new ParameterFieldReference();
//name of the parameter in report is “SalesOrderNumber”
orderid.ParameterName = "SalesOrderNumber";
orderid.FieldAlias = "salesorderid";
parameters[0]=ordered;

try
{
//call the CreateDataSubscription method for the class to create the
//subscription
string subscriptionID = rs.CreateDataDrivenSubscription
(report, settings, dataRetrieval, description, EventType, matchData, parameters);
MessageBox.Show(subscriptionID);
}
catch (SoapException ex)
{
MessageBox.Show(ex.Detail.InnerText.ToString());
}


4 comments:

Anonymous said...

I am learning SSRS and need to do data driven reports using standard edition and I like the idea of this. Does this code work? Is there a better current day approach to doing this? Thx.

Anonymous said...

It works perfectly. Make sure that extension parameters are case sensitive. so use upper case. E.G FILENAME, PATH, RENDER_FORMAT. Otherwise you will get soap exception.

Stuart Dobson said...

Great article and helped me a lot.

I am actually creating a whole program to mass create subscriptions based on this.

Could you please give some more information about how to build the scheduledefinition xml?

gksmk said...

Will this work in Express Edition of SQL Server 2008 R2