When that's converted to a string, you'll be missing the required single quotes from around your date (as you're passing it as a string).
You're better off passing the variable directly to an Execute SQL Task, then you don't need to convert it to a string and worry about the single quotes you're missing as you're passing the parameter in as its original type.
E.g. assuming you're using OLEDB, create an Execute SQL Task with the following in it:
Select Col1, Col2, Case when convert(date,col3) <= ?
Then 1
Else 0
End as Active
From my_table
Then add the parameter into the parameter mapping tab.
Have a look here for more info: