You should be able to get that working with your original approach if you just add in the single quotes and set the data flow source to your variable. e.g.:

"Select Col1, Col2, Case when convert(date,col3) <= '"+(DT_WSTR, 40) @[User::vEnd_Date] +"'

Then 1

Else 0

End as Active

From my_table"

But if you just need this Active flag as above, you could also avoid passing variables into the SQL entirely by using a very simple Derived Column Transformation in the data flow. Just bring Col3 in as a column in the select, then add a Derived Column, call it Active and put an expression like the below in:

col3 <= @[User::vEnd_Date] ? 1 : 0