When trying to use an input variable in my Execute SQL Task I was getting the following error:"Parameter name is unrecognized." and the package obviously failed.
After some playing around I noticed that it had something to do with the difference in variable mapping declaration based on your connection type.
Based on your connection type you have to choose the correct name in your Parameter Mapping window.
Here is a little overview (credits go to Kirk Haselden):
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!