(credits go to Kirk Haselden):
When using an imput variable in Execute SQL Task:
OLEDB takes ? in the query and a number as name (0, 1, ...)
ODBC takes ? in the query and a number as name (1, 2, ...)
ADO takes ? in the query and a @Variable as name
ADO.Net takes @Variable in both the query and the name
Also make sure to choose the correct datatype for the parameter!