none
SSIS-email

    Dotaz

  • Hi,

    The SQL task email below works fine but i don't know how to pass the email into the @recipients  for each customer and company, see the data set below

    DATASET

    SQL TASK

                 

    DECLARE @tableHTML  NVARCHAR(MAX) ;

    SET @tableHTML =
       N'<head>' +
        N'<style type="text/css">h2, body {font-family: Arial, verdana;} table{font-size:11px; border-collapse:collapse;} td{background-color:#F1F1F1; border:1px solid black; padding:3px;} th{background-color:#99CCFF;}</style>' +
       N'<h2><font color="#0000ff" size="4">Work Order Report</font></h2>' +   
       N'</head>' +
    N'<body>' +
    N' <hr> ' +
    N' ' +


        N'<table border="1">' +
        N'<tr><th>Company ID ID</th><th>Order</th>' +
        N'<th>Customer</th>

        </tr>' +
        CAST (( SELECT td = companyID, '',
                        td = saleOrder, '',
                        td = Customer
                  from OrdersEmail
                  FOR XML PATH('tr'), TYPE) AS NVARCHAR(max)) + 
        N'</table>' ;



    EXEC msdb.dbo.sp_send_dbmail @recipients='me@test.com',
        @profile_name = 'Systems Event Notice',
        @subject = 'Work Order List',
        @body = @tableHTML,
        @body_format = 'HTML' ;

    ====

    I need one email send to test@test.com; test1@test.com for company 100 for order SO145,SO146,SO147 
    and another email send to test5@test.com for company 200 for order SO145.
    How do I accomplish this?, please help- Thanks

    čtvrtek 14. června 2018 15:50

Všechny reakce

  • Have a query which gets you companyid, recipientlist values

    ie like

    SELECT companyID, email
    FROM YourTable

    Then use this in a execute sql task to populate a object variable

    Then have a for each loop to iterate through the object variable

    Inside the loop have two variables to store the individual values within the loop

    (@CompanyId,@RecepientList)

    then pass it your query like below

    DECLARE @tableHTML  NVARCHAR(MAX) ;
    
    SET @tableHTML =
       N'<head>' +
        N'<style type="text/css">h2, body {font-family: Arial, verdana;} table{font-size:11px; border-collapse:collapse;} td{background-color:#F1F1F1; border:1px solid black; padding:3px;} th{background-color:#99CCFF;}</style>' +
       N'<h2><font color="#0000ff" size="4">Work Order Report</font></h2>' +   
       N'</head>' +
    N'<body>' +
    N' <hr> ' +
    N' ' +
    
    
        N'<table border="1">' +
        N'<tr><th>Company ID ID</th><th>Order</th>' +
        N'<th>Customer</th>
    
        </tr>' +
        CAST (( SELECT td = companyID, '',
                        td = saleOrder, '',
                        td = Customer
                  from OrdersEmail
    Where companyID = ? FOR XML PATH('tr'), TYPE) AS NVARCHAR(max)) + N'</table>' ; EXEC msdb.dbo.sp_send_dbmail @recipients= ?, @profile_name = 'Systems Event Notice', @subject = 'Work Order List', @body = @tableHTML, @body_format = 'HTML' ;

    then map the two parameters (0 and 1) to variables@CompanyId,@RecepientList in the parameters tab


    Please Mark This As Answer if it solved your issue
    Please Vote This As Helpful if it helps to solve your issue
    Visakh
    ----------------------------
    My Wiki User Page
    My MSDN Page
    My Personal Blog
    My Facebook Page

    čtvrtek 14. června 2018 17:48
  • humm, i tried it but didn't get any email, but I hard code for the companyid and recipient then it's worked.  My guess is the value didn't pass into the loop.
    čtvrtek 14. června 2018 19:51
  • humm, i tried it but didn't get any email, but I hard code for the companyid and recipient then it's worked.  My guess is the value didn't pass into the loop.

    check if query returns values correctly to recordset variable

    Also make sure variable you use inside loop have correct datatypes and they're mapped correctly to the required indices


    Please Mark This As Answer if it solved your issue
    Please Vote This As Helpful if it helps to solve your issue
    Visakh
    ----------------------------
    My Wiki User Page
    My MSDN Page
    My Personal Blog
    My Facebook Page

    čtvrtek 14. června 2018 20:46