How to create Data driven report subscriptions in SQL Server Standard Version

By | April 2, 2014

I guess you are here because you are looking for a solution that will send your SSRS reports via email to a dynamic recipient list related to your data in your SSRS report.  In SQL Servers Business Intelligence and Enterprise Edition this feature is included but if you use a lower edition like Standard and Web well you are out of luck as this feature is not available.  Don’t loose hope though as there are lots of workarounds where we can recreate these function using only what you already have SSIS.

Lets begin, lets say you want to invoice your clients where your invoice is generated using SSIS with one parameter called Invoice Number

01 Invoice Sample

Now your issue is to send it via email like how the built-in subscription works but instead of a static recipient, you want to send it to whoever the invoice should be sent to, meaning dynamically generating it from the account holder information you might have on your database.  The best way to do this is by SSIS you can develop something on your own using C# or but the fastest, robust and most logical way of doing this is by using what SQL already have.

Lets start by creating an SSIS Project so open your Visual Studio and choose Integration Services Project

02 New Project

From here it will only take 3 main steps to create a full solution for your Data driven report subscriptions.

1. Loop through all items in your ADO NET Source using Foreach Loop Container

Lets now create a Data Flow task, drag one from the SSIS Toolbox

03 New Data Flow Task

Create a new connection, right-click on the Connection Managers and choose New Connection Manager.   This connection will be the connection to your list of Accounts for Invoicing, this should contain at least and email address and the parameter you are using on you your SSRS Report

04 New Connection

Choose ADO.NET Connection

05 New ADO.Net connection

Click new to create a new one

06 Configure Conneciton

Choose .Net ProvidersSqlClient Data Provider if you use SQL, provide the Server name and the Database you intend to use

07 Select Server

Create a variable, this will store the information of all Accounts ready for invoicing and call it “AccountsForInvoicing” and set the DataType to Object

08 Add Variable

Lets configure the data flow by adding an ADO NET Source and Recordset Destination, connect them together

09 Source to Destination

Set the ADO NET Source, choose the Connection you created and the table or view you want to use

10 Configure Source

Set your Recordset destination and save the records in the variable you created earlier.

11 Configure Destination

Choose the columns you want to use by going to the Input Columns

12 Input Columns

And that’s it, your Data Flow is configured

13 Warning Disappear

Now let create some additional string variables which will store individual items on the recordset while your SSIS loops.  Lets call them EmailAddress, InvoiceNumber and AccountName, I guess the names are self-explanatory

14 New Variables

Now lets loop through all recordset items you populated on your Data Flow Task.  Grab a Foreach Loop Container from your SSIS Toolbox.

15 For Loop

Set the Collection Properties as the following:

Enumerator : Foreach ADO Enumerator
ADO object source variable: The variable you stored your items on the Data Flow Task.  In this example it is the AccountsForInvoicing
Enumeration Mode : Rows in the first table

16 Configure For Loop

Map the recordset columns accordingly to the individual items variables you created earlier, the index should be ordered the way you query it from the database.

17 Variable Mapping

2. Generate SSRS reports and export to file using SSIS

Now were finished with our loop, let’s do something about it.
Lets now create additional variables that relates to reporting services:
SSRSReportPath : Basically the Path to your report, in our example it will be /Invoicing/Client Invoices
SSRSWebService : Your SSRS Service path usually it is http://{YourReportServerURL}/reportserver/reportexecution2005.asmx [YES IT IS STILL CALLED ReportExecution2005 even though its SQL Server 2012]
FilePath : The full file name with path of your exported report, this will be used for email attachment as well

18 Additional Variables

Now drag a Script Task from SSIS Toolbox

19 Script Task

Set the ReadOnlyVariables using the variables you created which are the SSRSReportPath, SSRSWebService, FilePath and InvoiceNumber.  Take note this variables will be used in creating and accessing the report so add everything you might need here.

Click Edit Script to continue.

20 Pass Variables

Add a service reference to your Reporting Services to get the methods you need.  Right click on the References and choose “Add Service Reference

21 Add Reference

Click Advanced

22 Advanced

Click “Add Web Reference

23 Web Reference

Choose the Report Execution URL, it will be http://{YourReportServerURL}/reportserver/reportexecution2005.asmx

Give it a Web Reference Name, in this example we call it ReportServiceReference  then click Add Reference.

24 Connect to reference

Reference the WebReference you created in your project

25 Referece the one you just added

Now let’s do some coding and dynamically generate SSRS report using C#

Change the Main section of your code with the one below

public void Main()
        // Report Server Settings
        var reportExecutionService = new ReportExecutionService();
        reportExecutionService.Credentials = System.Net.CredentialCache.DefaultCredentials;
        reportExecutionService.Url = Dts.Variables["User::SSRSWebService"].Value.ToString();
        var reportPath = Dts.Variables["User::SSRSReportPath"].Value.ToString();
        // Prepare Report Parameters
        var parameters = new ParameterValue[1];
        parameters[0] = new ParameterValue();
        parameters[0].Name = "InvoiceNumber";
        parameters[0].Value = Dts.Variables["User::InvoiceNumber"].Value.ToString();
        reportExecutionService.LoadReport(reportPath, null);
        reportExecutionService.SetExecutionParameters(parameters, "en-us");
        //Set Rendering Parameters
        var format = "PDF"//Choose to any format you want
        string encoding;
        string mimeType;
        string extension;
        Warning[] warnings = null;
        string[] streamIDs = null;
        byte[] result = reportExecutionService.Render(format, nullout extension, out mimeType, out encoding, out warnings, out streamIDs);
        //Create File
        var exportedFile = File.Create(Dts.Variables["User::FilePath"].Value.ToString(), result.Length);
        exportedFile.Write(result, 0, result.Length);
        Dts.TaskResult = (int)ScriptResults.Success;
    catch(Exception ex)
        Dts.TaskResult = (int)ScriptResults.Failure;

3. Dynamically send email with attachment using variables in SSIS

Now that you had created the reports its time to send it as an attachment, now grab that “Send Mail Task” in the SSIS Toolbox.

26 Send Email Task

Create a new SmtpConnection

27 New SMTP Connection

Declare your SMTP Server and other properties

28 Your SMTP Server

Now go to the Expressions tab since our data is dynamic.  Choose the FileAttachments, FromLine, Subject and ToLine based on the variables you created earlier.

30 Dynamic Variables

Once finished click OK, at this point there will be an error because it’s validating and your variables are dynamic.  To remove that red “X” icon from your Send Mail Task change the DelayValidation property to False

31 Remove Validation

That’s it your whole process is finished and to clean it up, just put understandable labels like such.

32 Final

Run it, in this test I used my own email on all accounts to see whether it sends.  And here it is!

33 Emails



20 thoughts on “How to create Data driven report subscriptions in SQL Server Standard Version

  1. Richard

    HI Raymund, great article and working my way through it. However I have hit a wall at the following part”Add a service reference to your Reporting Services to get the methods you need. Right click on the References and choose “Add Service Reference“

    Can you shed light on this, as in VS 2012 this option is not there.

    Help appreciated and I am sure once past this, it will work a treat


  2. Pingback: Uploading Files to SFTP Server using SSIS | Raymund Macaalay's Dev Blog

  3. Steve

    Is there a way to put the SSRS Report into the Body of the email address and not the attachment?

      1. Mike Christie

        This is a tremendously useful post; it was exactly what I was looking for and the detail and step-by-step instructions were very helpful as I’m fairly new to this technology.

        Raymund, can you give a little more detail on the answer above? I also need to have the report in the body of the email, not as a PDF; what do you mean by “under subscriptions”?

        Thanks for your help.

        1. Raymund Macaalay Post author

          Its in the Reporting Services Website if you choose a subscription its the MHTML option, this will embed it in the email body. Having said that its a different method as discussed in this article, this method means you already have a set recipient not a dataset

          1. Mike Christie

            What I was hoping for was a method of dynamically sending reports using datasets, with the resulting emails in the body of the report. One thing I could do is use reportingService2010.createSubscription — I could dynamically determine who to send the report to, and then create a subscription to run once in five minutes time in MHTML. That should send it in the body of the email. That seems clumsy, though. Could I do something like capture the output of reportExecutionService.Render in the script task and place it in the body of the email, rather than write the file to disk?

          2. Mike Christie


            I went ahead and created a version of data-driven reports that does some additional things — if you’re interested, contact me and I’ll send you the code. It embeds formatted output in email, which turns out to be surprisingly annoying to do — MHTML won’t do it. It also allows you to do conditional scheduling, meaning that the report only runs if a test condition is met. Since conditional scheduling is the only feature of SQL Server Enterprise I need at my site, this saves me from having to spend $25K to get Enterprise for my VM system. It’s not perfect, and I’m sure you could improve it, but it works.


          3. Raymund Macaalay Post author

            This sounds good 🙂
            I am in a holiday now, will definitely contact you as soon as O come back. Thanks for letting me know.

  4. Terese

    Great post. I was following step by step, and when I got to the Add service reference I received a message in the Web services found at this URL box of ‘The HTML document does not contain Web service discovery information’. This leaves the Add reference box greyed out and I can not click it. Can you help me understand what I need to do or if I’ve done something wrong?

    1. steve

      Hi Terese – I would guess that the address you used for your web service was probably incorrect? I also received this until I entered the report services web service address correctly… in my case I first tried with a named server but it needed the server IP.

  5. steve

    Thank you for an excellent post! We are stuck on Standard edition at my workplace so this solution was perfect…

  6. ash

    Hi Raymund,

    I am looking to create a data-driven report to email a list of people that meet certain condition. I also want to schedule the report to send the email on automated times, close lines with what Mike Christie was mentioning about. Any ideas on how to get it done?

    1. Mike Christie

      Ash, I’d be happy to send you (and anyone else interested) the code I wrote. My email is my first initial and my last name, at hmca dot com.

  7. Fred

    Hi Raymund,
    This is a great article with very clear steps; however, I hit a roadblock after adding the web reference for the ReportServiceReference wsdl. Ironically, all the methods are exposed and I’m able to add the code via intellisense without any issue. The problem occurs when I build the script task whereupon I get the following error in the reference.cs file: ‘ST_…Properties.Settings’ does not contain a definition for ‘ST_…ReportExecutionService’ and no extension method ‘ST_…ReportExecutionService’ accepting a first argument of type ‘ST_…Properties.Settings’ could be found (are you missing a using directive or an assembly reference?).

    The appropriate using statement for the ReportExecutionService is included in the same way you’ve listed in your Add Web Reference step.

    So, I’m stumped. Do you have any recommendations?

    Thanks in advance for your assistance,

  8. Fred

    I found the problem, my bad. I’d created the Rendering Parameters, specifically the Warning parameter, prior to when I’d initialized a new instance of the ReportExecutionService. So while the Warning parameter wasn’t flagged during the build, it definitely became evident when I hovered over the declaration statement.
    Thanks for getting back so promptly,

  9. Vinny

    Hi Raymund,

    Thank you very much for your solution. I try to setup SSRS email delivery report using your tutorial and come across an issue with passing datetime type parameters.
    The parameters[4], [5] and [6] values are datetime, how do I that as Value.ToString() not working?
    I am sorry but I am not familiar with C# programming.


    Please see below:

    // Prepare Report Parameters
    var parameters = new ParameterValue[9];
    parameters[0] = new ParameterValue();
    parameters[1] = new ParameterValue();
    parameters[2] = new ParameterValue();
    parameters[3] = new ParameterValue();
    parameters[4] = new ParameterValue();
    parameters[5] = new ParameterValue();
    parameters[6] = new ParameterValue();
    parameters[7] = new ParameterValue();
    parameters[8] = new ParameterValue();
    parameters[0].Name = “Family”;
    parameters[0].Value = Dts.Variables[“User::DFKEY”].Value.ToString();
    parameters[1].Name = “PayStatus”;
    parameters[1].Value = Dts.Variables[“User::PayStatus”].Value.ToString();
    parameters[2].Name = “Biller”;
    parameters[2].Value = Dts.Variables[“User::Biller”].Value.ToString();
    parameters[3].Name = “Overseas”;
    parameters[3].Value = Dts.Variables[“User::Overseas”].Value.ToString();
    parameters[4].Name = “StatementTransDate”;
    parameters[4].Value = Dts.Variables[“User::TransactionFrom”].Value.ToString();
    parameters[5].Name = “InvoiceDate”;
    parameters[5].Value = Dts.Variables[“User::InvoiceDate”].Value.ToString();
    parameters[6].Name = “DueDate”;
    parameters[6].Value = Dts.Variables[“User::DueDate”].Value.ToString();
    parameters[7].Name = “BuildingFund”;
    parameters[7].Value = Dts.Variables[“User::BuildingFund”].Value.ToString();
    parameters[8].Name = “PandF”;
    parameters[8].Value = Dts.Variables[“User::FOWSubs”].Value.ToString();

  10. Scott

    I know this post is old but I am getting a script error binary code any thoughts? SSIS 2012


Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.