have you tried above solution, it works for me

Parenthesis were not properly managed, corrected below
SELECT  FORMATMESSAGE('CTL|%s|%s|%s|', FORMAT(CURRENT_TIMESTAMP, 'yyyyMMdd'),CAST(SUM(totamount) AS varchar(MAX)),'100')
FROM  table.xxxx

'%s' --> signifies as dynamic message
since there are 3 of...

In SSIS package,
1. Store the SQL query in the variable, define it using expression
2. Connection Managers contains InitialCatalog property, if it is set dynamically (i.e., using expression)...

We can go for dynamic Sql, try below solution
-- generate a modest set of sample data
IF OBJECT_ID('tempdb..#Sample') IS NOT NULL
    DROP TABLE #Sample;

CREATE...

Look at below code, see if it give the correct results
DECLARE @SD DATETIME
    ,@cycle NUMERIC

SET @SD = '1-1-17'
SET @cycle = 21

SELECT CASE...

http://deleteduplicaterecords.blogspot.in/

You get all answers here