how to convert the below procedure into ssis for each loop and send mail task

  • I am tryting to convert the below procedure into SSIS, even if i get temp1 and temp2 into object variable.. how can i use those in for each loop container where it starts while condition in ssis?

    BEGIN TRY

    BEGIN

    Select

    PG.ID

    , PG.Time

    ,G.Address

    ,PG.Date

    ,b.Description as [D]

    ,b.Rec

    INTO #temp1

    FROM tblPep PG

    INNER JOIN tblRe RSV ON rsv.ID = PG.ID

    INNER JOIN tblDg G ON G.ID = RSV.ID

    inner join tblXsb on b.id = G.ID

    WHERE

    G.ID = -14

    SELECT

    Address

    , ROW_NUMBER() OVER(ORDER BY [Address]) AS [Cnt]

    INTO    #temp2

    FROM    #temp1

    GROUP BY Address

    DECLARE @html VARCHAR(8000)

    DECLARE @Rec VARCHAR(100)

    DECLARE @r INT

    SET @r = 1

    WHILE @r <= (SELECT COUNT(*) FROM #temp2)

    BEGIN

    SET @Rec = (SELECT [Address] FROM #temp2 WHERE [Cnt] = @r)

    SELECT @html = '<html><body style="font-family: ''EYInterstate Light'',Arial,sans-serif;">

    your id selected

    '

    SELECT @html = @html + '

    <table border="1"><tr><th>Time</th><th>ID</th><th>Desc</th><th>Sl</th></tr>'

    SELECT

    @html =

    @html +

    ('<tr><td width="200px;">' + CONVERT(VARCHAR, Time,0 )

    + '</td><td width="150px;"> ' + Cast(ID as varchar(15)) + '</td>' +

    + '</td><td width="250px;"> ' + [D] + '</td>' +

    '<td width="150px;"> ' + Rec+ '</td></tr>')

    FROM    #temp1

    WHERE   [Address] = @Rec

    SELECT @html = @html + '</table>

    <br/>'

    Select @html = @html + '----- https://covid.ey.net/ -----.'

    SELECT @html = @html + '

    </html>'

    DECLARE @Sub VARCHAR(100)

    SET @Sub = '-------'

    EXEC msdb.dbo.sp_send_dbmail

    @recipients = @Rec

    , @subject = @Sub

    , @body_format = 'html'

    , @body = @html

    SET @r = @r +1

    END

    END

    END TRY

    BEGIN CATCH

    BEGIN

    Declare @err nvarchar(1000)

    Select @err = ERROR_MESSAGE()

    EXEC msdb.dbo.sp_send_dbmail

    , @subject = 'Error'

    , @body_format = 'html'

    , @body =  @err

    END

    END CATCH

  • I am curious why you want to convert the procedure to an SSIS package? Is the procedure not working as needed?

     

  • We are getridding of EXEC msdb.dbo.sp_send_dbmail.. to SMTP send mail task..

  • Your procedure contains at least 1 syntax error. Why do you refuse to format your code?

    If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.

  • mcfarlandparkway wrote:

    We are getridding of EXEC msdb.dbo.sp_send_dbmail.. to SMTP send mail task..

    Not sure why since you are using Database Mail, which uses STMP.  I have a trigger on a database that is used to send emails and it works just fine.

     

  • USE [OTandE_Dev]
    GO

    /****** Object: Trigger [heartbeat].[HeartBeat_SendEmail] Script Date: 7/10/2020 10:00:28 AM ******/
    SET ANSI_NULLS ON
    GO

    SET QUOTED_IDENTIFIER ON
    GO

    CREATE TRIGGER [heartbeat].[HeartBeat_SendEmail]
    ON [heartbeat].[HeartBeat]
    AFTER INSERT
    AS
    BEGIN
    /**********************************************************************************************************************************************************************************************
    Trigger: [heartbeat].[HeartBeat_SendEmail]
    Author: Lynn A. Pettis
    Date: 2019-05-16
    Site: Polaris Alpha, A Parsons Company - Colorado Springs, CO

    This trigger is used to send HeartBeat emails to appropriate people regarding the status of monitored servers.

    An email will be sent on the status of a new server build and then on subsequent changes in status of a server or
    any of the installed applications on the server.

    Initally, only two emails are being generated and sent, one for system administrators and a second for project managers.

    Change Log:

    Date Developer JIRA Version Notes
    ========== ==================== ======== ======= =========================================================================================================================================
    2019-06-12 Lynn Pettis SI-544 1.1 Refactor trigger deleting program manager email.
    Modify trigger to use the email address inserted into the HeartBeat table.
    Comment reply to line. This was used to verify receipt of email during testing.
    **********************************************************************************************************************************************************************************************/
    DECLARE
    @AppName varchar(64)
    , @EmailNotification varchar(MAX)
    , @tableHTML1 nvarchar(MAX)
    , @tableHTMLFormat nvarchar(MAX)
    = N'

    Server Status Report


    <table border= "1">
    <tr>
    <th>Server (fqdn)</th>
    <th>Test Date and Time</th>
    <th>Application Name</th>
    <th>Application Test Date and Time</th>
    <th>Test Result</th>
    <th>Application Version</th>
    </tr>'; -- HTML header fragment used to generate the HTML message body for the HeartBeat Email notification.
    -- The rest of the HTML is generated in the following query.
    SET NOCOUNT ON;

    SELECT
    @AppName = [appName]
    , @EmailNotification = [EmailNotification]
    FROM
    [inserted];

    IF (@AppName = 'TestResult')
    BEGIN
    SET NOCOUNT ON;
    WITH [BaseDate] AS (
    SELECT
    [hb].[fqdn]
    , [batchDate] = CONVERT(
    varchar(30)
    , CAST(TODATETIMEOFFSET([hb].[batchDate], 0)AT TIME ZONE 'Mountain Standard Time' AS datetime)
    , 120
    )
    , [appName] = CASE
    WHEN [hb].[appName] = 'TestResult'
    THEN
    'Server Status'
    ELSE
    [hb].[appName]
    END
    , [testDate] = CONVERT(
    varchar(30)
    , CAST(TODATETIMEOFFSET([hb].[testDate], 0)AT TIME ZONE 'Mountain Standard Time' AS datetime)
    , 120
    )
    , [testResult] = CASE
    WHEN [hb].[testResult] = 0
    THEN
    'Down'
    WHEN [hb].[testResult] = 1
    THEN
    'Up'
    END
    , [hb].[appVersion]
    , [hb].[EmailNotification]
    , [previousTestResult] = [oa1].[testResult]
    , [previousServerCreationDateTime] = [oa1].[ServerCreationDateTime]
    FROM
    [heartbeat].[HeartBeat] AS [hb]
    INNER JOIN [inserted] AS [ins]
    ON [hb].[fqdn] = [ins].[fqdn] AND [hb].[batchDate] = [ins].[batchDate]
    OUTER APPLY (
    SELECT
    [hb2].[testResult]
    , [hb2].[ServerCreationDateTime]
    FROM
    [heartbeat].[HeartBeat] AS [hb2]
    WHERE
    [hb2].[appName] = [hb].[appName]
    AND [hb2].[batchDate] = (
    SELECT
    MAX([hb3].[batchDate])
    FROM
    [heartbeat].[HeartBeat] AS [hb3]
    WHERE
    [hb3].[batchDate] < [hb].[batchDate]
    AND [hb3].[fqdn] = [hb].[fqdn]
    AND [hb3].[ServerCreationDateTime] = [hb].[ServerCreationDateTime]
    )
    ) AS [oa1]
    WHERE
    [hb].[testResult] ^ ISNULL([oa1].[testResult], CAST(~ CAST([hb].[testResult] AS bit) AS int)) = 1 -- If Current testResult and previous testResult are the same, do not return the row (XOR condition)
    -- If oa1.testResult is null, use the inverse value of the current testResult to trigger an email.
    )
    SELECT
    @tableHTML1 = @tableHTMLFormat + CAST((
    SELECT
    = [bd].[fqdn]
    , ''
    , = [bd].[batchDate]
    , ''
    , = [bd].[appName]
    , ''
    , = [bd].[testDate]
    , ''
    , = [bd].[testResult]
    , ''
    , = [bd].[appVersion]
    FROM
    [BaseDate] AS [bd]
    ORDER BY
    [bd].[testDate]
    FOR XML PATH('tr'), TYPE
    ) AS nvarchar(MAX)) + N'</table>';

    IF @tableHTML1 IS NOT NULL
    EXEC [msdb].[dbo].[sp_send_dbmail]
    @recipients = @EmailNotification -- 'SI-OTandE@acompany.com'
    --, @reply_to = 'testreply@acompany.com'
    , @subject = 'System Admin - Server Status Report'
    , @body = @tableHTML1
    , @body_format = 'HTML';

    END;
    END; -- TRIGGER [dbo].[HeartBeat_SendEmail]
    GO

    ALTER TABLE [heartbeat].[HeartBeat] ENABLE TRIGGER [HeartBeat_SendEmail]
    GO


    This is the code that comprises the trigger.

  • mcfarlandparkway wrote:

    We are getridding of EXEC msdb.dbo.sp_send_dbmail.. to SMTP send mail task..

    Okay - no real reason to do this since it works from SQL Server, but to do this you are going to have to change your approach.

    1. Create a query that returns the results as needed - you need to remove both temp tables so you have just the rows needed with the correct address information.  You current approach groups by address and then joins back to #temp1 by that address.  This is problematic - what if you have 2 (or more) rows in #temp1 that match that address?
    2. Create a data flow in SSIS that uses this query (better yet, a stored procedure) for the source - and a recordset as the destination.
    3. Add a for-each container and use the recordset object created in step 2 (you need to create a variable to hold the recordset data).
    4. In the for-each, add all of the variables for each item in the recordset/data (yes, you have to create a variable for each column)
    5. Inside the for-each, add the SMTP object...configure it as needed using the variables populated in step 4.  You probably will need to build out a variable with the HTML code needed here, using the variables from step 4 - which can get quite complicated to build out in the SSIS expression editor.

    For something like this...I wouldn't use SSIS.  This really seems more like an SSRS solution with a data-driven subscription - a simple report with a parameter to filter based on your criteria and a simple query to return the list of address information (you can include TO, CC, BCC, SUBJECT, etc...).  The subscription could then be setup to send as MHTML so the report is embedded in the email.

     

    Jeffrey Williams
    “We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”

    ― Charles R. Swindoll

    How to post questions to get better answers faster
    Managing Transaction Logs

Viewing 7 posts - 1 through 7 (of 7 total)

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