Kirk has made a really useful reference post (here: http://sqljunkies.com/WebLog/knight_reign/archive/2005/10/05/17016.aspx) that explains the different ways to supply paramaters to your SQL statement in the Execute SQL Task depending on which connection manager you are using.
I think its worth making the point that if you are wanting to supply parameters like this then there is a method that works every time without fail and you don't have to remember differrent syntaxes for different providers. Simply build the whole SQL statement dynamically using a property expression on the SQLStatementSource property.
This may not work in all situations -like when using output parameters- but if you can use this method...you should do!
-Jamie