msdb.dbo.sp_send_dbmail running in SQL Agent

Hello

I have a SSIS package that all runs fine in Visual Studio, but fails when it runs in SQL Agent, most of the Tasks work and 95% of the time the package completes successfully, it just when it hits my Execute SQL task that has msdb.dbo.sp_send_dbmail in it. It will fail with this error;

Microsoft (R) SQL Server Execute Package Utility Version 14.0.2037.2 for 64-bit Copyright (C) 2017 Microsoft. All rights reserved. Started: 13:20:19 Error: 2024-10-29 13:20:58.73 Code: 0xC002F210 Source: Send Muliple Line errors to Accounts email Execute SQL Task Description: Executing the query "DECLARE @Todays_Count int DECLARE @copy_to varcha..." failed with the following error: "Failed to initialize sqlcmd library with error number -2147467259.". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly. End Error DTExec: The package execution returned DTSER_FAILURE (1). Started: 13:20:19 Finished: 13:20:58 Elapsed: 39.234 seconds. The package execution failed. The step failed.

This is what is in my Execute SQL task.

DECLARE @Todays_Count int

DECLARE @copy_to varchar(max)= '[email protected]'+';'+'[email protected]'+';'+'[email protected]'

SELECT @Todays_Count = (SELECT Count(*) as Error_Count

  FROM [DataBase].[dbo].[ABC]

  where Cast([nomval] as Money) = 0)

IF @Todays_Count > 0

  exec msdb.dbo.sp_send_dbmail @recipients = @copy_to, 

@subject = 'Invoice with Lines at zero value',

@body = 'The invoice details are below is an Invoice in Compleat that has zero value lines in the data.',

@query_result_header = 1,

@profile_name = 'Mail_Profile',

@query = N'Select description as Invoice_Error

from [DataBase].[dbo].[DEF]',

@execute_query_database = N'msdb'

I can't work out why. I have given the user that runs the Job task databasesendmail role under the MSDB, but that has not worked.

Any help would be great.