Trigger an Email of an SSRS Report from an SSIS Package

  • Jeff Moden

    SSC Guru

    Points: 996655

    Hey Stan,

    I just looked at your article and almost all of the graphics are coming across as solid blue boxes with a little red "x" inside a smaller box at the top left of the larger. Not sure if it's just my system or if the article code actually has a problem but I thought I'd give you a heads up.

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
    "Change is inevitable... change for the better is not".
    "If "pre-optimization" is the root of all evil, then what does the resulting no optimization lead to?"

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • Stan Kulp-439977

    SSCrazy Eights

    Points: 9977

    I believe that the sqlservercentral.com server is having severe issues. I have been writing an article and can't modify it or add or delete resource files. I had the web administrator upload a file for me last week, but now I need to change it and can't.

    I will email them again on Monday.

  • hkotamreddy

    SSC Rookie

    Points: 29

    Thank you for the detailed instructions. Are these steps specific to 2012? Will the same steps work in SQL Server 2008 R2?

  • Stan Kulp-439977

    SSCrazy Eights

    Points: 9977

    I don't have 2012 yet. I did it in 2008R2.

  • ajv849

    Valued Member

    Points: 58

    Hi - I am new to SSIS .

    Please correct me if I have misstated anything below:

    1) The SSIS package will be connecting to the report server DB.

    2) SSIS package can be placed on any server.

    Please let me know if there is any way one could trigger the subscriptions using ssis without connecting to the reportserver db. Say a webservice reference in the package. What should be the data connection for the package then.?

    Regards

    >>>>>>>>

    AJV

  • Stan Kulp-439977

    SSCrazy Eights

    Points: 9977

    ajv849 (4/10/2014)


    Hi - I am new to SSIS .

    Please correct me if I have misstated anything below:

    1) The SSIS package will be connecting to the report server DB.

    2) SSIS package can be placed on any server.

    Please let me know if there is any way one could trigger the subscriptions using ssis without connecting to the reportserver db. Say a webservice reference in the package. What should be the data connection for the package then.?

    Regards

    >>>>>>>>

    AJV

    You are running a native SQL Server stored procedure.

    EXEC ReportServer.ado.AddEvent @EventType='TimedSubscription',@EventData=subscriptionid

    You don't necessarily have to run it from SSIS. Any way you can remotely execute a stored procedure will work.

    I found this article that tells you how to do it from a web form or application:

    http://sqlserverbiblog.wordpress.com/tag/manually-starting-subscription/[/url]

    From the article: "You can use an ADO.NET SqlCommand object to execute this code from web form or application."

    Hope this helps.

  • ajv849

    Valued Member

    Points: 58

    Thank you .

    I do not have the accessiblity permission to the native sql proc.. So the only way left for me is to write a .net app to trigger the subscriptions.

    But I want to know what would be the best way of achieving this . SSIS trigger of a proc or use webservice in a .net app.

    Please let me know your thoughts.

    Regards

    >>>>>>>>>>

    AJV

  • Stan Kulp-439977

    SSCrazy Eights

    Points: 9977

    ajv849 (4/14/2014)


    Thank you .

    I do not have the accessiblity permission to the native sql proc.. So the only way left for me is to write a .net app to trigger the subscriptions.

    But I want to know what would be the best way of achieving this . SSIS trigger of a proc or use webservice in a .net app.

    Please let me know your thoughts.

    Regards

    >>>>>>>>>>

    AJV

    I don't know what you mean by not having accessibility to ReportServer.ado.AddEvent.

    You have to execute that procedure from a SQL statement. The .NET SqlCommand class would do it.

    I found this link that might help:

    Executing Database Commands Asynchronously with ADO.NET 2.0

  • f3pal

    Valued Member

    Points: 54

    Good article, however did the SQL code work? I can't see it working as "ado.AddEvent"

  • arukannan

    SSC Eights!

    Points: 889

    Alternatively, we can capture the job id of that subscription.

    we can trigger it as a job in Execute Sql Task.

    E.g. : Execute Msdb..sp_start_job '4055734B-F633-425B-AA91-062FFE751A04'

  • Koen Verbeeck

    SSC Guru

    Points: 258965

    Koen Verbeeck (3/20/2013)


    Very useful article, thanks.

    Apparently I already forgot I read this article 2 years ago.

    Still useful 😀

    Need an answer? No, you need a question
    My blog at https://sqlkover.com.
    MCSE Business Intelligence - Microsoft Data Platform MVP

  • rodgertdodger

    SSC Journeyman

    Points: 76

    Just disable the subscription job and then execute it from T-SQL Statement Task or put the line of code into a SP that does some work and then immediately fires the report.

  • Thomas Schutte

    Mr or Mrs. 500

    Points: 589

    Another way to kick of a subscription is to use sp_start_job and kick off the job SQL server automatically creates for each subscription. The name typically has a generic GUID name, to identify the right one I put together this script some time ago. The column RunSubscription will give you the SQL statement kicking off the subscription:

    USE ReportServer;

    WITH XMLTabl AS

    (

    SELECT cc.Name

    , cc.Path

    , CAST(ss.ExtensionSettings AS XML) ExtensionSettings

    , ss.SubscriptionID

    , ss.Description

    , ss.LastRunTime

    from dbo.Subscriptions ss

    INNER JOIN dbo.Catalog cc

    ON ss.Report_OID = cc.ItemID

    )

    , xmlvalues AS

    (

    SELECT SubscriptionID

    , ParameterValue.value('(./Name/text())[1]','varchar(100)') AS Parameter

    , ParameterValue.value('(./Value/text())[1]','varchar(2000)') AS Value

    FROM XMLTabl tt

    CROSS APPLY tt.ExtensionSettings.nodes('//ParameterValue') Parameters(ParameterValue)

    )

    , mail AS

    (

    SELECT SubscriptionID

    , 1 RowID

    , CASE CHARINDEX(';', Value)

    WHEN 0 THEN LTRIM(RTRIM(Value))

    ELSE LTRIM(RTRIM(STUFF(Value,CHARINDEX(';', Value), LEN(Value),'')))

    END Mail

    , CASE CHARINDEX(';', Value)

    WHEN 0 THEN ''

    ELSE LTRIM(RTRIM(STUFF(Value,1,CHARINDEX(';', Value),'')))

    END ToParse

    , Parameter

    FROM xmlvalues

    WHERE Parameter IN ('TO','CC','BCC')

    UNION ALL

    SELECT SubscriptionID

    , RowID + 1

    , CASE CHARINDEX(';', ToParse)

    WHEN 0 THEN LTRIM(RTRIM(ToParse))

    ELSE LTRIM(RTRIM(STUFF(ToParse,CHARINDEX(';', ToParse), LEN(ToParse),'')))

    END Mail

    , CASE CHARINDEX(';', ToParse)

    WHEN 0 THEN ''

    ELSE LTRIM(RTRIM(STUFF(ToParse,1,CHARINDEX(';', ToParse),'')))

    END ToParse

    , Parameter

    FROM mail

    WHERE LEN(ToParse) > 0

    )

    , enddate AS

    (

    SELECT ss.SubscriptionID

    , CAST(CAST(MatchData AS XML).value('(//*:EndDate/text())[1]','varchar(100)') AS DATE) SubscriptionEndDate

    FROM dbo.Subscriptions ss

    CROSS APPLY (SELECT CAST(MatchData AS XML)

    FROM dbo.Subscriptions

    WHERE SubscriptionID = ss.SubscriptionID) ca(XML)

    CROSS APPLY XML.nodes('//*:ScheduleDefinition') cb(DOW)

    )

    SELECT aa.Name SSRS_Name

    , 'EXEC msdb.dbo.sp_start_job ''' + jj.name + '''' [RunSubscription]

    , aa.Path Report_Path_On_Server

    , ff.Value ReportFormat

    , aa.LastRunTime

    , ee.SubscriptionEndDate

    , (SELECT COUNT(*) FROM mail WHERE mail.SubscriptionID = aa.SubscriptionID) [Number_Of_Recipients]

    , STUFF((SELECT '; ' + RTRIM(Parameter + ':' + Mail)

    FROM mail mm

    WHERE aa.SubscriptionID = mm.SubscriptionID

    GROUP BY SubscriptionID

    , Mail

    , Parameter

    ORDER BY Parameter DESC, Mail

    FOR XML PATH('')),1,2,'') Mail_Recipients

    , ISNULL(cc.Value,'') Mail_Text

    , aa.Description Subscription_Description

    , aa.SubscriptionID

    , jj.name JobName

    , jj.job_id Job_ID

    FROM XMLTabl aa

    INNER JOIN dbo.ReportSchedule ss

    ON aa.SubscriptionID = ss.SubscriptionID

    INNER JOIN msdb.dbo.sysjobs jj

    ON Convert(nvarchar(128),ss.ScheduleID) = jj.name

    LEFT JOIN xmlvalues ff

    ON aa.SubscriptionID = ff.SubscriptionID

    AND ff.Parameter = 'RenderFormat'

    LEFT JOIN xmlvalues cc

    ON aa.SubscriptionID = cc.SubscriptionID

    AND cc.Parameter = 'Comment'

    LEFT JOIN enddate ee

    ON aa.SubscriptionID = ee.SubscriptionID

    OPTION (MAXRECURSION 0)

  • ashish.shevale

    Ten Centuries

    Points: 1117

    keyser soze-308506 (3/19/2013)


    Hi

    I need to send distinct report to distinct persons

    I have a dataset of people, with their names, email, etc

    including the parameters to run the report

    Could I accomplish this with SSxS ?

    regards

    Yes

    Just create data driven subscription rather than timed subscription. Put all the email in one table and query them. The specific parametered report can go to specific email too. You even dont need SSIS for it . Only SSRS can do it

    ------------------------------------------------------------------------------------

    Ashish

  • akljfhnlaflkj

    SSC Guru

    Points: 76202

    Thanks for the training.

Viewing 15 posts - 16 through 30 (of 34 total)

You must be logged in to reply to this topic. Login to reply