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
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 VB.net 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
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
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
Choose ADO.NET Connection
Click new to create a new one
Choose .Net ProvidersSqlClient Data Provider if you use SQL, provide the Server name and the Database you intend to use
Create a variable, this will store the information of all Accounts ready for invoicing and call it “AccountsForInvoicing” and set the DataType to Object
Lets configure the data flow by adding an ADO NET Source and Recordset Destination, connect them together
Set the ADO NET Source, choose the Connection you created and the table or view you want to use
Set your Recordset destination and save the records in the variable you created earlier.
Choose the columns you want to use by going to the Input Columns
And that’s it, your Data Flow is configured
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
Now lets loop through all recordset items you populated on your Data Flow Task. Grab a Foreach Loop Container from your SSIS Toolbox.
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
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.
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
Now drag a Script Task from SSIS Toolbox
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.
Add a service reference to your Reporting Services to get the methods you need. Right click on the References and choose “Add Service Reference”
Click Advanced
Click “Add 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.
Reference the WebReference you created in your project
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() { try { // 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, null, out 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); exportedFile.Flush(); exportedFile.Close(); 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.
Create a new SmtpConnection
Declare your SMTP Server and other properties
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.
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
That’s it your whole process is finished and to clean it up, just put understandable labels like such.
Run it, in this test I used my own email on all accounts to see whether it sends. And here it is!
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
Regards
I am using VS 2012. This Add Service Reference is in the Script Task section of the project
Pingback: Uploading Files to SFTP Server using SSIS | Raymund Macaalay's Dev Blog
Is there a way to put the SSRS Report into the Body of the email address and not the attachment?
Yes you can but I havent tried this method, by default you can do it under subscriptions
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.
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
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?
Raymund
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.
Mike
This sounds good 🙂
I am in a holiday now, will definitely contact you as soon as O come back. Thanks for letting me know.
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?
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.
Thank you for an excellent post! We are stuck on Standard edition at my workplace so this solution was perfect…
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?
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.
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,
Fred
Can you send me a screenshot of your solution explorer as well as your ScriptMain.cs
Raymund,
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,
Fred
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.
Thanks……
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();
I know this post is old but I am getting a script error binary code any thoughts? SSIS 2012