Send db mail task fails in SSIS Package with errors related to the parameter

Question

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.

"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='dev@null.com',
@subject=@SUB,
@body=@BODY,
@query= 'SELECT DISTINCT FileLoadName
FROM [dbo].[Table1] WHERE RowCDate >=@PackageStartTime',
@attach_query_result_as_file=1

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?

Thank in advance

score 0 · Answer 1 · answered Mar 12 '13 at 01:32

0

I am fairly sure that you cannot pass a parameter into a script like that from SSIS, but you can pass it into a stored procedure. So wrap all of that up in a stored procedure with a parameter. The following code creates your stored procedure.

Then you call it with

EXEC p_MyProc ?

in your package

Also note I think you set @SUB twice - I changed it to @BODY

Also please note it is usually a bad idea to store dates as varchar(250) - change it to date or datetime now if you have the opportunity.

CREATE PROC p_MyProc
@PackageStartTime Varchar(250)
AS

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 sp_send_dbmail @profile_name='default',
@recipients='dev@null.com',
@subject=@SUB,
@body=@BODY,
@query= 'SELECT DISTINCT FileLoadName
FROM [dbo].[Table1] WHERE RowCDate >=@PackageStartTime',
@attach_query_result_as_file=1

GO

answered Mar 12 '13 at 01:32

Nick.Mc

18,304
6
61
91

I know. Does this answer your question? – Nick.Mc Mar 12 '13 at 03:26
I can't create new stored procedures. Is there any other solution? – ETLUser Mar 12 '13 at 03:27
You can move all of the logic in to SSIS – Nick.Mc Mar 12 '13 at 04:38
Since I can't create the stored procedure here. How should i do it? I am sorry I am new to SSIS. – ETLUser Mar 12 '13 at 16:08
See my guide in my other answer on how to create this logic in SSIS – Nick.Mc Mar 12 '13 at 23:08

score 0 · Answer 2 · answered Mar 12 '13 at 04:45

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

Create an execute SQL Task with this in it:

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

Pass your parameter in, and capture the result into two variables, (single row returned)
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

Send db mail task fails in SSIS Package with errors related to the parameter

2 Answers2

Linked