1

Topic: SSRS and mailing on an e-mail

Is available SSRS the receipt report on utilities. Well as it is necessary, entering and proceeding balance, charges, detailing on recomputations, indications individual and  registration instruments, etc.
In the course of optimization came to that all report made on one table (Matrix), and the stored procedure became data source.
And here I faced with . On the one hand, to form these receipts faster, than they are printed by the stream printer, the stored procedure has been optimized under formation of tens thousand receipts at one start (less than 40 milliseconds on the receipt). Speed of formation separately one receipt thus became about 1 second.
For the stream printer all became good. And here for mailing of single receipts on electronic mail - all is bad.
Accordingly, I reflect over following variants:
1. Somehow to make a variant optimized under one receipt. Then it will be possible to deliver means SSRS on a subscription data-driven.
2. To save result in PDF together with any latent information (an e-mail the address, a subject, a letter body) and then any  the utility to break this PDF from ten thousand receipts on ten thousand files which already to send on an e-mail.
Who faced such task?
What are still possible ways of its decision?

2

Re: SSRS and mailing on an e-mail

it is clear that became bad
Divide 2 tasks and solve them separately proceeding
Can in SSIS write a packet or even a SQL Server through dbmail.
Only to black lists do not get:)

3

Re: SSRS and mailing on an e-mail

bbx1389;
Badly that on one receipt to form on SSRS very slowly. Even if to finish till 0.5 seconds on the receipt, writing of separate procedure (what not to eat any more well) then even 100  receipts I will deliver 14 hours that it's really too much.
And it is not clear, at what here SSIS? At me the receipt form on SSRS with all , a QR-code and other advertizing evil spirits.

4

Re: SSRS and mailing on an e-mail

ptr128, ssrs to you then a little than helps, if controlled subscriptions are unsuitable...
Problem in speed of rendering or data acquisition?
Try, as well as has been told to break into tasks:
1. Data acquisition of all),
2. Using local processing ReportViewer   the data and  in separate pdf.
3. Received kindly send on mail.
P.S.: I do not know... As there with a multithreading at usage ReportViewer control, but it is possible to try  2+3 (it is possible to try SSIS to make a packet)
P.P.S.: and how many formation and sending of the mail message occupies? You as spamer can  on  smile

5

Re: SSRS and mailing on an e-mail

buser;
I cannot have a local processing because is not present . At me is only SQL the server from SQL Server Agent and the server for SSRS.
Tasks and so are divided. The stored procedure on SQL forms in one output flow unlimited (within the size tempdb) an amount of receipts. 10 thousand without problems are formed less, than for 400 seconds.
Problem in speed of rendering of the single receipt. In one flow SSRS 10 thousand receipts  in PDF for half an hour. Launching 8 flows on 4 kernels I receive time of rendering less, than 40 msec on the receipt. It is natural, while SSRS  I can on SQL easy prepare them the next portion in stored procedure.
And here the single receipt in PDF SSRS though kill, faster than for 500 msec (0.5 ) not .
That is, forming eight PDF, each of which contains 10  receipts, I spend 6 minutes for stored procedure and 6 minutes on rendering in eight flows on SSRS. And here if I from this flow begin  under one receipt in PDF 8 flows SSRS at me will be  more hour that in any gate does not climb any more.
All right, started to study already iTextSharp. If it turns out to break quickly it one big PDF into set small, sending them  on SMTP on that and I will stop.

6

Re: SSRS and mailing on an e-mail

buser wrote:

P.P.S.: and how many formation and sending of the mail message occupies? You as spamer can  on  smile

To me it on a fig. It already a problem of that supplier of utilities which delivers these receipts)
SMTP at it on the good server costs. Should consult easily with such flows.
About time to tell while I can nothing, as exim consults explicitly faster, than SSRS

7

Re: SSRS and mailing on an e-mail

More shortly so. Eyes are afraid - hands do. The subject can be marked [SOLVED]
For two hours wrote the utility on C# which breaks one huge PDF with a heap of receipts, on set small, containing under one receipt and at once them sends on electronic mail.
Now particulars.
1. In data source should be:
- Unique number of the document
- The electronic address of the receiver or the list of the receivers divided by a comma (everything that is admissible in the field To: an e-mail)
- Message subject
- The message text
2. In SSRS the report the field with a white font on a white field (that it was visible not) and type size in 1 point of following contents is allocated, for example, in the first line:
- Marker of the beginning of a field from four characters "&$#!"
- Unique number of the document (only digits)
- Separator of "%"
- The electronic address of the receiver encoded in base64
- Separator of "%"
- The subject of the letter encoded in base64
- Separator of "%"
- Letter body,  in base64
- Marker of the end of a field from four characters "&$#!"
Coding in base64 should be used, as it appeared to master the elementary method cyrillic in PDF.
At me it turned out so:

 =iif (Max (Fields! customeremail. Value, "proc_id_group") = "", "", "&$#!" +Str (Fields! proc_id. Value) + "%" +
System. Convert. ToBase64String (System. Text. Encoding. UTF8.GetBytes (Max (Fields! customeremail. Value, "proc_id_group"))) + "%" +
System. Convert. ToBase64String (System. Text. Encoding. UTF8.GetBytes (Iif (Max (Fields! emailsubj. Value, "proc_id_group") = "", "(without a subject)";
Replace (
Replace (
Replace (
Replace (
Replace (
Replace (
Replace (
Replace (
Max (Fields! emailsubj. Value, "proc_id_group");
"%", Max (Fields! customername. Value, "proc_id_group");
),"%", Max (Fields! companyname. Value, "proc_id_group");
), "%", Max (Fields! externalcontractid. Value, "proc_id_group");
), "%", Choose (Max (Fields! repmonth. Value, "proc_id_group");
"January", "February", "March", "April", "May", "June", "July", "August", "September", "October", "November", "December"
) + "" + CStr (Max (Fields! repyear. Value, "proc_id_group"));
), "%", FormatDateTime (Max (Fields! duedate. Value, "proc_id_group"), DateFormat. ShortDate);
), "%", FormatNumber (Max (Fields! dr_amt. Value, "proc_id_group") +Max (Fields! rc_amt. Value, "proc_id_group"), 2)
), "%", FormatNumber (Max (Fields! ou_amt. Value, "proc_id_group"), 2)
), "%%", "%")
)))+"%" +System. Convert. ToBase64String (System. Text. Encoding. UTF8.GetBytes (Iif (IsNothing (Max (Fields! emailbody. Value, "proc_id_group")), "(without a body)";
Replace (
Replace (
Replace (
Replace (
Replace (
Replace (
Replace (
Replace (
Max (Fields! emailbody. Value, "proc_id_group");
"%", Max (Fields! customername. Value, "proc_id_group");
),"%", Max (Fields! companyname. Value, "proc_id_group");
), "%", Max (Fields! externalcontractid. Value, "proc_id_group");
), "%", Choose (Max (Fields! repmonth. Value, "proc_id_group");
"January", "February", "March", "April", "May", "June", "July", "August", "September", "October", "November", "December"
) + "" + CStr (Max (Fields! repyear. Value, "proc_id_group"));
), "%", FormatDateTime (Max (Fields! duedate. Value, "proc_id_group"), DateFormat. ShortDate);
), "%", FormatNumber (Max (Fields! dr_amt. Value, "proc_id_group") +Max (Fields! rc_amt. Value, "proc_id_group"), 2)
), "%", FormatNumber (Max (Fields! ou_amt. Value, "proc_id_group"), 2)
), "%%", "%")
)))+"&$#!") 

Set Replace () are necessary for changeover  in a subject or a body of the letter values from the report.
The utility has three parameters:
1. The address or domain name SMTP of the server for sending of letters and through a colon - port number on the server; if the port number is specified, sending of letters will be produced on STARTTLS, and otherwise - on 25 port without SSL
2. An e-mail of the sender of the message;
3. A way to PDF to a file
For the utility the request strongly feet not to beat, as it is my code first in life on C#. The main thing - works)

using System;
using System. Collections. Generic;
using System. Linq;
using System. Text;
using System. IO;
using System.Net;
using System. Net. Mail;
using System. Net. Mime;
using System. Net. Security;
using System.Security.Cryptography.X509Certificates;
using System. Threading. Tasks;
using iTextSharp.text;
using iTextSharp.text.pdf;
using iTextSharp.text.pdf.parser;
namespace ConsoleApplication1
{
class Program
{
static void Main (string [] args)
{
int rc = 0;
if (args. Length! = 3)
{
Console. WriteLine ("Usage: pdf_to_e-mail_splitter.exe smtp-server [:port] sender-e-mail PDF-file-to-parse");
Environment. Exit (12);
}
try
{
rc=parse_file (args [0], args [1], args [2]);
}
catch (Exception ex)
{
Console. WriteLine ("Error parsing input PDF file {0}. {1}", args [2], ex. ToString ());
Environment. Exit (16);
}
Environment. Exit (rc);
}
static int parse_file (string smtpServer, string emailFrom, string fname)
{
int rc = 0;
PdfReader reader = new PdfReader (fname);
Document document = null;
PdfCopy copy = null;
string pagePath = null;
string rawPage;
SmtpClient client;
string [] decoded = null;
string proc_id = "";
ServicePointManager.ServerCertificateValidationCallback =
delegate (object s, X509Certificate certificate, X509Chain chain, SslPolicyErrors sslPolicyErrors) {return true;};
string [] smtpName = smtpServer. Split (': ');
if (smtpName. Length> 1)
{
client = new SmtpClient (smtpName [0], Convert. ToInt32 (smtpName [1]));
client. EnableSsl = true;
client. Credentials = CredentialCache.DefaultNetworkCredentials;
client. UseDefaultCredentials = true;
Console. WriteLine ("Connecting to SMTP with SSL");
}
else
{
client = new SmtpClient (smtpName [0]);
client. EnableSsl = false;
Console. WriteLine ("Connecting to SMTP without SSL and authentication");
}
client. DeliveryFormat = SmtpDeliveryFormat. International;
for (int pageNo = 1; pageNo <= reader. NumberOfPages; pageNo ++)
{
rawPage = PdfTextExtractor. GetTextFromPage (reader, pageNo);
try
{
decoded = parse_page (rawPage);
}
catch (Exception ex)
{
Console. WriteLine ("[Page {0} skipped: {1}]", pageNo, ex. Message);
continue;
}
if (decoded [0]! = proc_id)
{
Console. WriteLine ("Page {0} decoded as document No {1} for AN e-mail address {2}", pageNo, decoded [0], decoded [1]);
if (proc_id! = "")
{
document. Close ();
document. Dispose ();
MailMessage message = new MailMessage (emailFrom, decoded [1], decoded [2], decoded [3]);
Attachment data = new Attachment (pagePath, MediaTypeNames.Application.Pdf);
message. Attachments. Add (data);
try
{
client. Send (message);
}
catch (Exception ex)
{
Console. WriteLine ("Exception caught in SmtpClient. Send: {0}", ex. ToString ());
rc = 4;
}
data. Dispose ();
message. Dispose ();
File. Delete (pagePath);
}
pagePath = System. IO.Path. GetTempPath () + System.IO.Path.GetFileNameWithoutExtension (fname) + "_page.PDF";
document = new Document ();
copy = new PdfCopy (document, new FileStream (pagePath, FileMode. Create));
document. Open ();
copy. AddPage (copy. GetImportedPage (reader, pageNo));
}
else
{
Console. WriteLine ("Page {0} decoded as document No {1} and appended for AN e-mail address {2}", pageNo, decoded [0], decoded [1]);
copy. AddPage (copy. GetImportedPage (reader, pageNo));
}
proc_id = decoded [0];
}
if (proc_id! = "")
{
document. Close ();
document. Dispose ();
MailMessage message = new MailMessage (emailFrom, decoded [1], decoded [2], decoded [3]);
Attachment data = new Attachment (pagePath, MediaTypeNames.Application.Pdf);
message. Attachments. Add (data);
try
{
client. Send (message);
}
catch (Exception ex)
{
Console. WriteLine ("Exception caught in SmtpClient. Send: {0}", ex. ToString ());
rc = 4;
}
data. Dispose ();
message. Dispose ();
File. Delete (pagePath);
}
return (rc);
}
static string [] parse_page (string page_raw)
{
string [] decoded = new string [4];;
int from = page_raw. IndexOf ("&$#!");
if (from <0) throw new ArgumentException ("Label &$#! not found");
int to = page_raw. IndexOf ("&$#!", from+4);
if (to <0) throw new ArgumentException ("Label &$#! found only once");
string found = page_raw. Substring (from + 4, to - 4);
string [] parsed = found. Split (' % ');
if (parsed. Length! = 4) throw new ArgumentException ("Delimiter % must exist only two times");
for (int i=0; i <4; i ++)
{
decoded [i] = (i == 0)? parsed [i]: Encoding. UTF8.GetString (Convert. FromBase64String (parsed [i]));
}
return decoded;
}
}
}

If the e-mail for the document is defined, in the beginning of each sheet at it our latent line will be filled. If it is not defined - the line will be empty.
If the document occupies some sheets the utility defines it on repetition of number of the document on the subsequent sheets.
With a speed all is very good. The library iTextSharp very quickly) splits up PDF

8

Re: SSRS and mailing on an e-mail

ptr128, a suitable variant.