Send SSRS report in email body programmatically

Question

I have an SSRS report that accepts a parameter, based on this parameter the report should be sent in an email body to a specific address. There can be hundreds of different addresses that these can be sent to. We are using SQL Server Standard Edition.

Based on this, the following is true:

• We cannot use data driven subscriptions (Only available in Enterprise edition)
• We cannot set up multiple subscriptions for each potential recipient (There could be hundreds)
• We do not want to send the report as an attachment (Needs to be the body of the email)

I know we can call a subscription from SQL Server or via .Net code, but we cannot change the recipient as far as I can see. Our best solution at the minute is to create a HTML string, outside of SSRS, with the appropriate formatting and add that to the body of an email. In doing this it means that a developer needs to be on hand to create the string if anything needs changed, or if a new report needs created, so it is not very flexible for anyone not familiar with HTML.

So is there any other way to create a report in SSRS and send it in the body of an email to a specified email address, based on a parameter value?

Answer 1

The short answer is yes, but its not simple. Here's something I developed for SQL 2008.

Firstly to make the report appear in the email body you just need to output it using the MHTML renderer. This can be parameterised too.

Next you'll need an SSIS package with a script task that can run the report and produce the required output.

Here's a snippet of the VB script you'll need with SSIS:

(Trolls forgive me for using VB. I'd only ever use C# these days)

First method for saving the file.

Protected Sub SaveFile(ByVal url As String, ByVal localpath As String)
    Dim loRequest As System.Net.HttpWebRequest
    Dim loResponse As System.Net.HttpWebResponse
    Dim loResponseStream As System.IO.Stream
    Dim loFileStream As New System.IO.FileStream(localpath, System.IO.FileMode.Create, System.IO.FileAccess.Write)
    Dim laBytes(256) As Byte
    Dim liCount As Integer = 1
    Try

        loRequest = CType(System.Net.WebRequest.Create(url), System.Net.HttpWebRequest)
        loRequest.Credentials = System.Net.CredentialCache.DefaultCredentials
        loRequest.Timeout = 99999 '1 minute
        loRequest.Method = "GET"
        loResponse = CType(loRequest.GetResponse, System.Net.HttpWebResponse)
        loResponseStream = loResponse.GetResponseStream
        Do While liCount > 0
            liCount = loResponseStream.Read(laBytes, 0, 256)
            loFileStream.Write(laBytes, 0, liCount)
        Loop
        loFileStream.Flush()
        loFileStream.Close()
    Catch ex As Exception
    End Try
End Sub

Second method to call the SSRS report in the required format and save using the first method.

Public Sub Main()
    Dim url, destination As String
    Dim FileExtension As String
    Dim RenderAs As String

    'default to avoid nulls
    FileExtension = ".NULL" 'http://msdn.microsoft.com/en-gb/library/ms154606.aspx

    RenderAs = Dts.Variables("FileType").Value.ToString

    If RenderAs = "EXCEL" Then
        FileExtension = ".xls"
    ElseIf RenderAs = "WORD" Then
        FileExtension = ".doc"
    ElseIf RenderAs = "PDF" Then
        FileExtension = ".pdf"
    ElseIf RenderAs = "MHTML" Then
        FileExtension = ".mhtml"
    ElseIf RenderAs = "CSV" Then
        FileExtension = ".csv"
    ElseIf RenderAs = "IMAGE" Then
        FileExtension = ".tif"
    End If

    'create ssrs url
    'url = "http://hisrs01/ReportServer/Pages/ReportViewer.aspx?%2fCombined+Reports+-+HIS%2f14-15+SSoTP+Staff+Level+Weekly+Activity&rs:Command=Render&StaffGroup=" + Dts.Variables("varRSParameter1").Value.ToString + "&Provider=" + Dts.Variables("varRSParameter2").Value.ToString + "&rs:Format=Excel"
    url = Dts.Variables("ReportURL").Value.ToString + "&rs:Format=" + Dts.Variables("FileType").Value.ToString

    'create destination
    destination = Dts.Variables("TempFilePath").Value.ToString + "\Reports Created\" + Dts.Variables("FileName").Value.ToString + FileExtension

    'System.Threading.Thread.Sleep(5000)

    'write url out to test file (debugging)
    'strFile = "D:\Test\" + Replace(Dts.Variables("varRSParameter1").Value.ToString, "+", " ") + " - " + Replace(Dts.Variables("varRSParameter2").Value.ToString, "+", " ") + ".txt"
    'File.AppendAllText(strFile, url)

    SaveFile(url, destination)

    Dts.TaskResult = ScriptResults.Success

End Sub

You'll need to use SSIS package variables to handle how the report is produced, in what format and from where.

Then I created a stored procedure to call the SSIS package with the values needed. This then used the SQL Server database mail to collect the SSIS produced file, attach it and off you go with recipients handled by the dbmail rather than an SMTP call from SSRS subscriptions.

Here's a the procedure.

CREATE PROCEDURE [dbo].[EmailSSRSReport]
    (
    @Event VARCHAR(50) = 'Test',
    @ReportURL NVARCHAR(500),
    @FileType VARCHAR(10) = 'MHTML',
    @FileName VARCHAR(100) = 'Rendered SSRS Report',
    @Debug BIT = 0
    )
AS

BEGIN

    --local variables
    DECLARE @Cmd NVARCHAR(500)
    DECLARE @EmailAddresses NVARCHAR(500)
    DECLARE @PackagePath NVARCHAR(255)
    DECLARE @FullFilePath NVARCHAR(500)
    DECLARE @FinalBodyText VARCHAR(MAX)
    DECLARE @FinalSubject VARCHAR(MAX)
    DECLARE @CmdOutput TABLE
        (
        [Output] NVARCHAR(500) NULL
        )

    --set and get parts for report and email    
    SELECT
        @EmailAddresses = [Notifications].[dbo].[fn_GetEmailAddresses](@Event),
        @PackagePath = [dbo].[fn_GetProperty]('SSISPackageLocation'),
        @FullFilePath = [dbo].[fn_GetProperty]('ReportsOutputFolder') + @FileName + 
            CASE UPPER(@FileType)
                WHEN 'EXCEL' THEN '.xls'
                WHEN 'WORD' THEN '.doc'
                WHEN 'PDF' THEN '.pdf'
                WHEN 'MHTML' THEN '.mhtml'
                WHEN 'CSV' THEN '.csv'
                WHEN 'IMAGE' THEN '.tif'
            END,
        @FinalBodyText = 'Please see attached the requested SSRS report <strong>' + @FileName + '</strong>.<br/><br/>Kind regards<br/><br/>S&SHIS Data Management<br/><a href="mailto:datamanagement@sshis.nhs.uk?subject=SSRS Report Auto Email">DataManagement@sshis.nhs.uk</a>',
        @FinalSubject = 'Auto Alert For ' + @FileName + '. ' + CONVERT(VARCHAR, GETDATE(), 103)

    SET @Cmd = 'dtexec /f "' + @PackagePath + 'Run SSRS Report.dtsx" /set \package.variables[ReportURL].Value;"' + @ReportURL + '" /set \package.variables[FileName].Value;"' + @FileName + '" /set \package.variables[FileType].Value;"' + @FileType + '"'

    --add styling
    SET @FinalBodyText = 
    '
    <html>
    <head>
        <style type="text/css">
            body
                {
                font-family: "calibri";
                font-size: 16px;
                }
        </style>
    </head>
    <body>
    ' + @FinalBodyText +
    '</body>
    </html>'

    --run command to produce SSRS report with params
    INSERT INTO @CmdOutput
    EXEC [master].sys.xp_cmdshell @Cmd

    --check cmd output for errors
    IF EXISTS
        ( 
        SELECT
            *
        FROM
            @CmdOutput
        WHERE
            [Output] LIKE '%error%'
        )
        BEGIN
            RAISERROR('Error executing command, run procedure in debug mode o view output.',16,1)
            RETURN;
        END

    --output details in debug mode
    IF @Debug = 1
        BEGIN
            SELECT @Cmd AS 'Cmd'

            SELECT
                *
            FROM
                @CmdOutput
        END

    --send email
    EXEC msdb.dbo.sp_send_dbmail 
        @recipients = @EmailAddresses,
        @subject = @FinalSubject,
        @body = @FinalBodyText,
        @file_attachments = @FullFilePath,
        @body_format = 'HTML'; 

END
GO

This gives you complete flexibility way to run any SSRS report and send it to whoever. But it is a lot of effort and over engineering to achieve a workaround to the current inflexible out of the box functionality.

Lastly I'd recommend iterating over a config table contains email address data etc using the procedure above.

Of course other bespoke report parameters can be added to with this approach if you want.