MTSDL
asked on
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 = 'Test@test.com',
@copy_recipients = 'test1@test.com',
@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
EXEC msdb.dbo.sp_send_dbmail
@profile_name = 'SQL Mail',
@recipients = 'Test@test.com',
@copy_recipients = 'test1@test.com',
@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
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Hmm, ok not sure what is going on... how about giving this a try:
@body = ?,
@body = 'The number is: ' + @body,
@body = ?,
@body = 'The number is: ' + @body,
ASKER
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
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
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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.