How to pass variable as a parameter in Execute SQL Task SSIS?

In your Execute SQL Task, make sure SQLSourceType is set to Direct Input, then your SQL Statement is the name of the stored proc, with questionmarks for each paramter of the proc, like so:

Click the parameter mapping in the left column and add each paramter from your stored proc and map it to your SSIS variable:

Now when this task runs it will pass the SSIS variables to the stored proc.

The EXCEL and OLED DB connection managers use the parameter names 0 and 1.

I was using a oledb connection and wasted couple of hours trying to figure out the reason why the query was not working or taking the parameters. the above explanation helped a lot Thanks a lot.

Along with @PaulStock's answer, Depending on your connection type, your variable names and SQLStatement/SQLStatementSource Changes

https://docs.microsoft.com/en-us/sql/integration-services/control-flow/execute-sql-task