I think I’m writing this as much for my sake as for others. Since I continually forget this setting here is how you pass in Parameters into SP’s your calling in your Execute SQL tasks’s in SSIS. In particular for this one I’m passing date’s which is common.
When you have a Execute SQL Task to set the paratemer your passing in as a Variable in SSIS here’s the Steps i’ve been using.
Create the Variable in SSIS, SSIS>Variables>NewIcon> Give it a name and type it should be scoped most likely as the whole package. That does depend on your complexity of the package.
Add your Execute SQL Task> When entering your Stored Procedure use Question marks for the parameters seperated by a comma. Then hit the parameters button and in the parameter name use each variable name from the stored procedure in the same order as you have Them in the stored procedure. Choose the Variable that it corresponds to on the other side. So you might see this.
Hope that helps!