Appending text to a variable in SSIS

I have an "Execute SQL Task" in an dtsx package that sends an email using the following code...
EXEC msdb.dbo.sp_send_dbmail
      @profile_name = 'SQL Mail',
       @recipients = '',
      @copy_recipients = '',
      @body = ?,
      @subject = 'Some title' ;
This works fine, however I want to add more text to the body (as ? only supplies a single counter value)
I have tried a number of different versions of the code... the following is what I am trying to do...
      @body = 'The number is: ' ?,
However this is not working... neither is @body = 'The number is: ' + ?,
If anyone out there knows how to get around this it would be much appreciated (the input variable ? is of type varchar)
Thanks in advance
Who is Participating?
digital_thoughtsConnect With a Mentor Commented:
What is the error you get when you try:

@body = 'The number is: ' + ?

Just taking a guess it has to do with trying to add a string and a counter value together, you might just need to CAST the counter value to a string first:

@body = 'The number is: ' + CAST(? AS VARCHAR(10))
MTSDLAuthor Commented:
I have tried the above example and there was an error for that as well.

The following is the error that I get...
Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.
Hmm, ok not sure what is going on... how about giving this a try:

@body = ?,
@body = 'The number is: ' + @body,
MTSDLAuthor Commented:
I have tried the above code and that did not work either...

What I have done is the following... and it works a treat

SELECT 'The number is: ' + cast(count(*) as varchar) AS CountText FROM Table

I pass CountText to the next "Execute SQL Task" and it works fine
digital_thoughtsConnect With a Mentor Commented:
Yes, that would work just fine, any time you need to add a non-string (varchar) value to a varchar you will need to cast or convert it. I should have asked what exactly ? was as it wasn't directly clear in your originally post. Regardless, glad you got it to work.
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.