Use sp_Send_DBMail with Variable

Transact-SQL
1

Hi everyone,
The last task in my SSIS package needs to send an email which shows the number of files that were copied.
That value is stored in variable @CountOfFilesCopied. The var has package scope but I get error

"[Execute SQL Task] Error: Executing the query "Declare @Body1 varchar(250);
Set @Body1 = 'Nbr of..." failed with the following error: "Parameter name is unrecognized.". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.

Declare @Body1 varchar(250);
Set @Body1 = 'Nbr of files copied: ' + CONVERT(varchar(30), @CountOfFilesCopied) <--- error here
EXEC msdb.dbo.sp_send_dbmail
@profile_name = 'MyProfile',
@body = @Body1,
@body_format ='HTML' ,
@recipients = 'Me',
@subject = 'Job Has Completed';

2

Just to confirm - you are not using the built-in SMTP task in SSIS and instead are using SQL Server to send the mail.

Which means you are using an Execute SQL Task to execute the stored procedure sp_send_dbmail. To do that you need to define the parameters and assign them to the parameter placeholder. In the Parameters pane - add the SSIS variable, set it as input with the data type set to LONG. You can also use @CountOfFilesCopied as the parameter name - but leave the length as -1.

In your code:

Declare @CountOfFilesCopied int = ?;
Declare @Body1 = concat('Nbr of files copied: ', cast(@CountOfFilesCopied As varchar(10)));

Execute msdb.dbo.sp_send_dbmail
        @profile_name = 'MyProfile'
      , @body = @Body1
      , @body_format = 'HTML'
      , @recipients = 'Me'
      , @subject = 'Job Has Completed';

Or - you can use the built-in SMTP component and use an expression to reset the body, or you can use a script task and use .NET to send the email.

2 Likes
4

Thanks @jeffw8713 I have looked for examples of what I'm trying to do but comeup short.
Here is my code and the parameter configuration:

Declare @Body1 varchar(250);
Declare @CountOfFilesCopied int = ?;
Declare @Body1 = concat('Nbr of files copied: ', cast(@CountOfFilesCopied As varchar(10)));

EXEC msdb.dbo.sp_send_dbmail
@profile_name = MyProfile',
@body = @Body1,
@body_format ='HTML' ,
@recipients = 'Me@Me.com',
@subject = 'The job Has completed';

The Parameter setting:
image

Error in the SQL task:
[Execute SQL Task] Error: Executing the query "Declare @Body1 varchar(250);
Declare @CountOfFiles..." failed with the following error: "Parameter name is unrecognized.". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.

I'm sure I'm missing 1 critical piece.
Thanks

5

The parameter setting - add the @ to the Parameter Name so it becomes @CoutOfFilesCopied. You are also declaring @Body1 twice - remove the first declare and modify the second declare so it has the correct data type:

Declare @Body1 varchar(250) = concat('Nbr of files copied: ', cast(@CountOfFilesCopied As varchar(10)));
1 Like
6

That did it @jeffw8713 .

I added @ to the parameter
image

The Send Mail works now. Thank you.