SSIS: Working with Parameters in the Execute SQL Task (ADO.NET)

Using Parameters with Stored Procedures

reference: MSDN

Using Parameters with ADO.NET and ADO Connection ManagersADO.NET and ADO connection managers have specific requirements for SQL commands that use parameters:
ADO.NET connection managers require that the SQL command use parameter names as parameter markers. This means that variables can be mapped directly to parameters. For example, the variable @varName is mapped to the parameter named @parName and provides a value to the parameter @parName.

ADO.NET Set IsQueryStoreProcedure is set to True.

To provide values to parameters, variables are mapped to parameter names. Then, the Execute SQL task uses the ordinal value of the parameter name in the parameter list to load values from variables to parameters.