It should work when you create the SQL statement in a string variable and use that variable as the source for your SQL statement in the OLE DB source.
Make sure the EvaluateAsExpression property of the variable is set to true.
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP