Export SSRS to PDF as Attachment (not via subscriptions)

Question

Good day,

Currently i am exporting my SSRS2016 report in html via ssis to internal and external users

Dim client As New WebClient()
client.Credentials = CredentialCache.DefaultNetworkCredentials
Dim content = client.DownloadString("http://sbh-XXXX/ReportServer/Pages/ReportViewer.aspx?%2fDashboard&rS:Format=MHTML&rs:Command=Render&rc:Toolbar=None&rc:HeaderArea=None&rc:MHTMLFragment=True&rc:OutlookCompat=True&rc:ExpandContent=True")

I had a previous solution of having the pdf format link in a text file that opens in PFD but the users hated the text file and i could not find the solution, please help i want to via ssis attach the pdfredendered report in email attachment

please help?

regards

Answer 1

Thank you
I dont really want to send mail via the script task
Without the email I had variable attachmentpdf = pdf but receiving conversion error when changing type
The ssis var attachmentpdf was string, object and byte but then tried email in script

How do I do without email script but I update the ssis Var is read write

Answer 2

Render the report in PDF as you normally would. That is an option to the rendered when you request the report. Right now you get HTML back, you'll be getting PDF instead. At this point everything remains the same. Create an email with whatever body you want. Attach the PDF that you got back from SSRS as an attachment and then send the email.

You didn't mention how you're sending the email but I assume you're using the Send Mail task. It has an option to add an attachment. You'll just need to store the generated report temporarily. Note that there is a warning that if the attachment doesn't exist when the package runs then it'll error but I assume it means when the task runs, which would make sense.

If for some reason you cannot get the Send Mail task to work then you can do the same thing programmatically using a script.

Answer 3

Thank You, im not sure i follow

Dim content = client.DownloadString("http://sbh-XXXX/ReportServer/Pages/ReportViewer.aspx?%2fDashboard&rS:Format=PDF&rs:Command=Render&rc:Toolbar=None&rc:HeaderArea=None&rc:MHTMLFragment=True&rc:OutlookCompat=True&rc:ExpandContent=True

how would i download the file automatically from ssis rendered and then attach it in send mail task
the above is rendered in SSIS Script task, should i be saving the export pdf from script task
aslo it would need to overwrite or generate a filename that sis can pickup

please provide example or ?

Answer 4

You won't be using DownloadString here as that returns the binary file as a string. Use DownloadData instead. You get back the byte array. Place that into an SSIS variable you have defined in your package (or container). Inside the Send Mail Task go to the attachments section and set it using an expression that points to the SSIS variable your script has set.

   byte[] pdf = client.DownloadData(url);   
     
   Dts.Variables["AttachmentPdf"].Value = pdf;  

Note that to write to the variable you need to modify the script properties in the UI and add the variable(s) you are writing to the ReadWriteVariables section. Otherwise it'll fail at runtime.

You could also send the email directly from the script task if you really wanted to but I think it is more maintainable to use the Send Mail task so it is clear in the package.

Answer 5

WOW - Thank You, this looks very interesting :)
going to try this tomorrow :)