生成HTML邮件正文的存储过程在SSIS中调用报错如何解决

问题排查及解决步骤 #

1. 直接报错原因 #

你调用存储过程的语句写了EXEC dbo.GenerateEmailBody ?带了参数占位符，但你的原始存储过程没有定义任何输入/输出参数，所以直接抛出「参数名称无法识别」的错误。同时原始存储过程默认返回值为int类型的执行状态码，所以你拿不到HTML正文结果。 #

2. 第一步：修改存储过程，增加输出参数 #

将存储过程调整为带输出参数的形式，直接返回生成好的HTML字符串，修改后代码如下：

CREATE PROCEDURE GenerateEmailBody
    @EmailBody NVARCHAR(MAX) OUTPUT -- 新增输出参数，用于返回HTML邮件正文
AS
BEGIN
    DECLARE @xhtmlBody XML,
            @tableCaption VARCHAR(30) = 'Orderlist';

    SET @xhtmlBody = (SELECT (
SELECT cus.CustomerNumber as CustomerNumber,cus.Location as ReceivingLocation, 
       i.StrainName as StrainName,i.StrainCode as StrainCode,i.Age as Age,
       i.Sex as Sex,i.Genotype as Genotype,i.RoomNumber as SentFrom,io.OrderQuantity as OrderQuantity
FROM [dbo].[MouseOrder] mo
JOIN [dbo].[Customer] cus on cus.Customer_ID = mo.CustomerId
JOIN [dbo].[InventoryOrder] io on io.OrderId = mo.MouseOrder_ID
JOIN [dbo].[Inventory] i on i.Inventory_ID =  io.InventoryId 
WHERE mo.OrderDate = convert(date,getdate() AT TIME ZONE 'UTC' AT TIME ZONE 'Eastern Standard Time') and mo.SAMAccountEmail = 'abc.def@inc.org'
FOR XML PATH('row'), TYPE, ROOT('root'))
.query('<html><head>
            <meta charset="utf-8"/>
            <style>
            table {border-collapse: collapse;  width: 100%;} 
            th {background-color: #4CAF50; color: white;}
            th, td { text-align: left; padding: 8px;}
            tr:nth-child(even) {background-color: #f2f2f2;}
         </style>
         </head>
         <body>
<table border="1">
   <caption><h2>{sql:variable("@tableCaption")}</h2></caption>
   <thead>
      <tr>
        <th>Cust Numb</th>
        <th>Rec Location</th>
        <th>Strain</th>
        <th>Strain Code</th>
        <th>Age</th>
        <th>Sex</th>
        <th>Genotype</th>
        <th>Sent From</th>
        <th>Order Quantity</th>
      </tr>
   </thead>
   <tbody>
{
    for $row in /root/row
    return <tr>
            <td>{data($row/CustomerNumber)}</td>
            <td>{data($row/ReceivingLocation)}</td>
            <td>{data($row/StrainName)}</td>
            <td>{data($row/StrainCode)}</td>
            <td>{data($row/Age)}</td>
            <td>{data($row/Sex)}</td>
            <td>{data($row/Genotype)}</td>
            <td>{data($row/SentFrom)}</td>
            <td>{data($row/OrderQuantity)}</td>
        </tr>
}
</tbody></table></body></html>'));

    -- 将XML转为字符串赋值给输出参数
    SET @EmailBody = CAST(@xhtmlBody AS NVARCHAR(MAX));
END
GO

你可以先在SSMS中手动测试存储过程是否正常：

DECLARE @html NVARCHAR(MAX);
EXEC dbo.GenerateEmailBody @html OUTPUT;
SELECT @html;

确认返回的结果是完整的HTML代码即可。 #

3. 第二步：配置SSIS的Execute SQL Task #

按以下要求调整任务配置即可解决报错：

• 「General」页配置：
  • ResultSet 选择 Single Row
  • SQLStatement 填写：EXEC dbo.GenerateEmailBody ? OUTPUT
• 「Parameter Mapping」页配置：
  • 新增参数映射，变量选择你预先定义的User::EmailData（变量类型需为String）
  • Direction 选择 Output
  • Data Type 选择 NVARCHAR
  • Parameter Name 填 0（OLE DB连接使用位置占位符，第一个参数索引为0）
  • 参数大小填 -1（对应NVARCHAR(MAX)的长度）
    无需配置Result Set页，HTML内容会直接写入你指定的User::EmailData变量，后续C#脚本直接调用该变量发送邮件即可，你现有的C#代码已经开启了IsBodyHtml = true，不需要额外修改。

内容的提问来源于stack exchange，提问作者user4912134