Populate HTML Email Template with Data from SQL Server Query via Orchestrator

Question

I am attempting to set up a process in Orchestrator that will generate and send an email from an HTML template. However, I also want to be able to add data to that template from the results of a SQL Server query.

For example, here is a section of the template where I want to add data:

<tr>
    <td align="right" valign="top">
        <font color="#2684AB" face="arial" />
        File:
    </td>
    <td align="left" valign="top">
        <font color="#2684AB" face="arial" />
        &nbsp; <!-- Data goes here (field is named FileName) -->
    </td>
</tr>

The only problem is that I'm not sure how to go about this. I know how to call the query, but I don't know how to add the data to the template. If this helps at all, I also know all of the fields where I am pulling data from. Any suggestions? I should also add that I am relatively new to working in Orchestrator.

score 0 · Answer 1 · answered Oct 31 '17 at 01:47

It's my code sample:

      SET @tableHTML =@tableHTML+'<h3>'+@Title+N'</h3>' +
            N'<tr style="background-color: #248157;color: white;height: 40px;">
            <th style="border: 1px solid #ccc">Ext PONo</th><th style="border: 1px solid #ccc">Order ID</th>
            <th style="border: 1px solid #ccc">Client</th><th style="border: 1px solid #ccc">Amount</th><th style="border: 1px solid #ccc">Order Date</th>
            <th style="border: 1px solid #ccc">Status</th><th style="border: 1px solid #ccc">Hold Reason</th>
            <th style="border: 1px solid #ccc">Hold User</th><th style="border: 1px solid #ccc">Hold Date</th>
            </tr>'+CONVERT(NVARCHAR(max),(
                SELECT '<tr style='+CASE WHEN ROW_NUMBER()OVER(ORDER BY OrderID)%2=0 THEN  '"background-color: #ffffff;"' ELSE '"background-color: #f2f2f2;"' END +'>'
                      ,[td/@style]='border: 1px solid #ccc',td=ExtPO,''
                      ,[td/@style]='border: 1px solid #ccc',td='<a href=http://xxxxxxxxxxxx='+LTRIM(OrderID)+'>'+LTRIM(OrderID)+'</a>',''
                      ,[td/@style]='border: 1px solid #ccc',td=LTRIM(ClientID)+'-'+ClientName,''
                      ,[td/@style]='border: 1px solid #ccc',td=ISNULL(SubTotal,0),''
                      ,[td/@style]='border: 1px solid #ccc',td=ISNULL(CONVERT(VARCHAR,OrderDate,101),'')+'<BR><font color=red>Held '+LTRIM(HeldCount)+' time(s)</font>',''
                      ,[td/@style]='border: 1px solid #ccc',td=OrderStatus+ISNULL('<br><font color=red>In Flag:'+FlagStatus+'</font>',''),''
                      ,[td/@style]='border: 1px solid #ccc',td=CASE WHEN HoldReasonCode='290' THEN REPLACE(HoldReason,'!',' of <a href=http://xxxxxxxxx?orderID='+LTRIM(ParentOrderID)+'>'+LTRIM(ParentOrderID)+'</a>!') ELSE HoldReason END+ISNULL('<BR><font color=red>'+Comments+'</font>','')  ,''
                      ,[td/@style]='border: 1px solid #ccc',td=HoldUser,''
                      ,[td/@style]='border: 1px solid #ccc',td=ISNULL(CONVERT(VARCHAR,HoldDate,101),''),'','</tr>'
                FROM #HeldOrder WHERE SourceCode=@SourceCode ORDER BY HoldReasonCode
                FOR XML PATH(''),TYPE
            ))+N'</table>'



IF LEN(@tableHTML)>0
    BEGIN
        SET @HTMLContent='<!DOCTYPE html>
        <html><head>
        <style>
        table, td, th {border: 1px solid #ccc;font-family:Cambria}
        table {border-collapse: collapse;width: 100%;}
        th {background-color: #248157;color: white;height: 40px}
        td {height: 40px;vertical-align: middle;}
        .even {background-color: #f2f2f2;}
        .odd {background-color: #ffffff;}
        tr:nth-child(even) {background-color: #f2f2f2}
        </style></head>
        <body>

        <p> </p>'
        SET @HTMLContent=@HTMLContent+@tableHTML+'<BR>'+CONVERT(VARCHAR,GETDATE())+'</body></html>'

Populate HTML Email Template with Data from SQL Server Query via Orchestrator

1 Answers1