• An execute SQL task is different from a source because the execute SQL task doesn't care about metadata and resultsets retrieved, but for the source this is very important.

    That's true but is not related to how parameters are set up. The two paradigms are far too different in this regard (e.g. positional vs named). One piece of documentation that could really help for the Execute SQL task is a table mapping SSIS variable types to the SQL data types in the ESQL task parameter mapping tab. Actually I'd like to see a three-way mapping:

    SSIS variable type SSIS Parameter type SQL Data type

    decimal CURRENCY money

    ...

    Some are obvious, some less so...