Programmatically emailing of SSRS reports

Recently I was tasked with creating a process to email SSRS reports out to users.

Ordinarily I would suggest using the built in subscription manager, however in this case we needed to be able to increment any date parameters in any report, and we have 300 of them.

I won’t go in to the details of how we managed the incrementing of parameters in this particular blog (that may come in a future blog), and instead will focus on how I went about automating the process of emailing the reports to the specified end users.

Below  is a screenshot of my SSIS package, the first step is simply calling a stored procedure which obtains from a table in SQL Server the names of any reports to be run and emailed out to users.  This package runs hourly, so the stored procedure is looking for reports that have been marked to run at XX:XX:XX time on XXX day of the week. The results are a report name, an email address, an output location, the format of the report

image

The second step runs within a for each loop which runs for each row returned from the stored procedure in step. This step executes a VB script to run the report and save it to a file share, a copy of which can be found below:

   1: Imports System

   2: Imports System.Data

   3: Imports System.Math

   4: Imports Microsoft.SqlServer.Dts.Runtime

   5: Imports System.Net.HttpWebRequest

   6: Imports System.Net.HttpWebResponse

   7: Imports System.Net

   8:  

   9: <Microsoft.SqlServer.Dts.Tasks.ScriptTask.SSISScriptTaskEntryPointAttribute()>

  10: <System.CLSCompliantAttribute(False)> _

  11: Partial Public Class ScriptMain

  12:     Inherits Microsoft.SqlServer.Dts.Tasks.ScriptTask.VSTARTScriptObjectModelBase

  13:  

  14:     'Dim reportpath As String = "E:\SSRS Projects\Repors to be emailed" '

  15:  

  16:     Enum ScriptResults

  17:         Success = Microsoft.SqlServer.Dts.Runtime.DTSExecResult.Success

  18:         Failure = Microsoft.SqlServer.Dts.Runtime.DTSExecResult.Failure

  19:     End Enum

  20:  

  21:  

  22:     ' The execution engine calls this method when the task executes.

  23:     ' To access the object model, use the Dts property. Connections, variables, events,

  24:     ' and logging features are available as members of the Dts property as shown in the following examples.

  25:     '

  26:     ' To reference a variable, call Dts.Variables("MyCaseSensitiveVariableName").Value

  27:     ' To post a log entry, call Dts.Log("This is my log text", 999, Nothing)

  28:     ' To fire an event, call Dts.Events.FireInformation(99, "test", "hit the help message", "", 0, True)

  29:     '

  30:     ' To use the connections collection use something like the following:

  31:     ' ConnectionManager cm = Dts.Connections.Add("OLEDB")

  32:     ' cm.ConnectionString = "Data Source=localhost;Initial Catalog=AdventureWorks;Provider=SQLNCLI10;Integrated Security=SSPI;Auto Translate=False;"

  33:     '

  34:     ' Before returning from this method, set the value of Dts.TaskResult to indicate success or failure.

  35:     ' 

  36:     ' To open Help, press F1.

  37:     Private Sub savefile(ByRef soutname As String, ByRef url As String)

  38:  

  39:         Dim loRequest As System.Net.HttpWebRequest

  40:         Dim loResponse As System.Net.HttpWebResponse

  41:         Dim loResponseStream As System.IO.Stream

  42:         Dim loFileStream As New System.IO.FileStream(soutname, System.IO.FileMode.Create, System.IO.FileAccess.Write)

  43:         Dim laBytes(256) As Byte

  44:         Dim liCount As Integer = 1

  45:  

  46:         Try

  47:             '   System.Windows.Forms.MessageBox.Show(url.ToString)

  48:             loRequest = CType(System.Net.WebRequest.Create(url), System.Net.HttpWebRequest)

  49:             loRequest.Credentials = System.Net.CredentialCache.DefaultCredentials

  50:             'loRequest.Credentials = New NetworkCredential("mparamasivam", "Desktop100", "IDEAS")

  51:             loRequest.Timeout = 600000

  52:             loRequest.Method = "GET"

  53:             loResponse = CType(loRequest.GetResponse, System.Net.HttpWebResponse)

  54:             loResponseStream = loResponse.GetResponseStream

  55:             '  System.Windows.Forms.MessageBox.Show("a")

  56:             Do While liCount > 0

  57:                 liCount = loResponseStream.Read(laBytes, 0, 256)

  58:                 loFileStream.Write(laBytes, 0, liCount)

  59:             Loop

  60:             loFileStream.Flush()

  61:             loFileStream.Close()

  62:  

  63:             'System.Windows.Forms.MessageBox.Show("b")

  64:         Catch ex As Exception

  65:             'System.Windows.Forms.MessageBox.Show(ex.Message)

  66:             Dts.Events.FireError(-1, "savefile", ex.Message.ToString, String.Empty, 0)

  67:         End Try

  68:     End Sub

  69:     Public Sub Main()

  70:         '

  71:         ' Add your code here

  72:         '

  73:         Dim url, destination As String

  74:         Try

  75:             url = "http://sql###01/ReportServer/Pages/ReportViewer.aspx?%2fReports%2f" + Dts.Variables("ReportName").Value + "&rs:Command=Render&rs:Format=" + Dts.Variables("Format").Value + ""

  76:             destination = Dts.Variables("Output").Value.ToString

  77:             savefile(destination, url)

  78:  

  79:         Catch ex As Exception

  80:             'System.Windows.Forms.MessageBox.Show(ex.Message.ToString)

  81:             Dts.Events.FireError(-1, "Main", ex.Message.ToString, String.Empty, 0)

  82:         End Try

  83:  

  84:         Dts.TaskResult = ScriptResults.Success

  85:  

  86:     End Sub

  87:  

  88:  

  89: End Class

  90:

The final step is to email the report to the recipient obtained in step 1:

   1: Imports System

   2: Imports System.Data

   3: Imports System.Math

   4: Imports System.Net.Mail

   5: Imports Microsoft.SqlServer.Dts.Runtime

   6:  

   7: <Microsoft.SqlServer.Dts.Tasks.ScriptTask.SSISScriptTaskEntryPointAttribute()>

   8: <System.CLSCompliantAttribute(False)> _

   9: Partial Public Class ScriptMain

  10:     Inherits Microsoft.SqlServer.Dts.Tasks.ScriptTask.VSTARTScriptObjectModelBase

  11:  

  12:     Enum ScriptResults

  13:         Success = Microsoft.SqlServer.Dts.Runtime.DTSExecResult.Success

  14:         Failure = Microsoft.SqlServer.Dts.Runtime.DTSExecResult.Failure

  15:     End Enum

  16:  

  17:   

  18:     Public Sub Main()

  19:         '

  20:         ' Add your code here

  21:         '

  22:         Dim varMail As MailMessage

  23:         Dim varSMTPClient As SmtpClient

  24:         Dim varMailBody As String

  25:         Dim varAddresses As String = (Dts.Variables("Recipent").Value.ToString)

  26:         Dim nindex As Integer = 0

  27:  

  28:         Dim address As String() = Split(varAddresses, ";")

  29:  

  30:         '      System.Windows.Forms.MessageBox.Show(address(0).ToString)

  31:  

  32:         varMailBody = "Hello,<br><br>"

  33:         varMailBody += "Please find attached your requested report <br><br>"

  34:         varMailBody += "If you have any questions please contact IT via email (support@adventureworks.co.uk). <br><br>"

  35:         varMailBody += "Kind Regards."

  36:         varMail = New MailMessage()

  37:         varMail.From = New MailAddress("reports.viewer@adventureworks.co.uk")

  38:  

  39:         'For nindex = 0 To UBound(address)

  40:         'varMail.To.Add(address(nindex).ToString)

  41:         'Next

  42:  

  43:         varMail.To.Add(varAddresses)

  44:  

  45:         varMail.Subject = Dts.Variables("Subject").Value.ToString

  46:         varMail.Body = varMailBody

  47:         varMail.IsBodyHtml = True

  48:  

  49:         Dim attachment As New Attachment(Dts.Variables("Output").Value.ToString)

  50:         varMail.Attachments.Add(attachment)

  51:  

  52:         varSMTPClient = New SmtpClient("MAIL####01")

  53:         'varSMTPClient.UseDefaultCredentials = True

  54:         'varSMTPClient.Credentials = System.Net.CredentialCache.DefaultNetworkCredentials

  55:  

  56:         Try

  57:  

  58:             varSMTPClient.Send(varMail)

  59:             Dts.TaskResult = ScriptResults.Success

  60:  

  61:         Catch ex As Exception

  62:             Dts.Events.FireError(-1, "MyPackageName", ex.ToString(), "", 0)

  63:             Dts.TaskResult = ScriptResults.Failure

  64:  

  65:         End Try

  66:  

  67:     End Sub

  68:  

  69: End Class

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s