SSIS work lesson of the morning: don’t forget to set the EvaluateAsExpression property to True on your variable when you have an expression to represent the variable value.
I had three variables representing similar SELECT statements to be used in data flow sources, so I copied and pasted the expressions from one variable into the others, and tweaked them as required. However, on one of the variables I forgot to set that EvaluateAsExpression property as well, being engrossed in the minor modifications of the similar Expression properties, I guess. About a half an hour wasted debugging that little miss.
Setting this property seems to be an unnecessary extra step if you’ve got an an expression in the Expression property, but, that’s SSIS for you.