发送数据库邮件任务在 SSIS 包中失败，并出现与参数相关的错误 [英] Send db mail task fails in SSIS Package with errors related to the parameter

问题描述

我正在尝试在 SSIS 包中执行此 sql 任务，该任务在找不到文件名时发送电子邮件.我已经在我的 ssis 包中声明了这个用户定义的变量@PackageStartTime".但是当我的 SSIS 包执行此任务时，它失败并出现以下错误.

I am trying to execute this sql task in SSIS package which send an email when the file name is not found. I have declared this user-defined variable "@PackageStartTime" in my ssis package. But when my SSIS package hit this task it fails with following error.

正在执行查询 DECLARE @PackageStartTime Varchar(250) SET @Packag...."失败并出现错误.:参数名称无法识别."可能的失败原因:查询有问题，ResultSet"属性设置不正确，参数设置不正确，或者连接建立不正确.

"Executing query DECLARE @PackageStartTime Varchar(250) SET @Packag...." failed with the error.: "Parameter name is unrecognized." Possible failure reasons: Problem with the query, "ResultSet" Property not set correctly, parameters not set correctly, or connection not established correctly."

DECLARE @PackageStartTime Varchar(250)
SET @PackageStartTime =?

IF(SELECT COUNT(*)
FROM [dbo].[Table1] WHERE RowCDate >=@PackageStartTime)>0

BEGIN

DECLARE @SUB Varchar(250)
SET @SUB = 'File Failed'+@@SERVERNAME

DECLARE @BODY Varchar(250)
SET @BODY = 'File Failed'+@@SERVERNAME

EXEC msdb.dbo.sp_send_dbmail @profile_name='default',
@recipients='[e-mail]',
@subject=@SUB,
@body=@BODY,
@query= 'SELECT DISTINCT FileLoadName
FROM [dbo].[Table1] WHERE RowCDate >=@PackageStartTime',
@attach_query_result_as_file=1

我无法理解.我刚刚添加了一个变量 User::strPackageStartTime 作为 Datatype = String 并且 Value 为空.我在执行 SQL 任务编辑器的参数映射中没有该变量.我错过了什么吗?

I am unable to understand. I have just added a variable User::strPackageStartTime as Datatype = String and Value is blank. I don't have that variable in parameter mapping in Execute SQL Task Editor. Is there I am missing something?

提前致谢

推荐答案

要将其移至 SSIS(请注意，我无法访问 SSIS，所以我瞎了眼)

To move this into SSIS (please note I do not have access to SSIS so I'm flying blind)

1. 在其中创建一个执行 SQL 任务:

SELECT COUNT(*) As RCount, 'File Failed' + @@SERVERNAME As MsgFROM [dbo].[Table1] WHERE RowCDate >= ?

SELECT COUNT(*) As RCount, 'File Failed' + @@SERVERNAME As Msg FROM [dbo].[Table1] WHERE RowCDate >= ?

1. 传入你的参数，并将结果捕获到两个变量中，(返回单行)

1. Pass your parameter in, and capture the result into two variables, (single row returned)

您的下一步是另一个执行 SQL 任务，使用您的变量调用 sp_send_dbmail.这被有条件地调用，因此它仅在前一步返回非零值时才运行

Your next step is another execute SQL Task, calling sp_send_dbmail with your variables. This is called conditionally so it only runs if the prior step returned a non zero value

这篇关于发送数据库邮件任务在 SSIS 包中失败，并出现与参数相关的错误的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！