3 most often reason, when you are using variables in Execute SQL task inside SSIS and get following error:

Error: 0xC002F210 ... , Execute SQL Task: Executing the query "...

..." failed with the following error: "HERE IS IMPORTANT PART". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.

Based on what error text is instead od "Here is imporant part", we can split reason and solutin into 3 parts

Parameter name is unrecognized

When you get error like 

Error: 0xC002F210 at yourtaskname task, Execute SQL Task: Executing the query "
..."" failed with the following error: "Parameter name is unrecognized.". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.

The Reason is wrongly used Parameter name in Parameter mapping. Most often used OLE DB needs as parameter name 0, 1, 2 ... see table bellow

Proper Using Parameter Names and Markers

for more information see Parameters and Return Codes in the Execute SQL Task in Microsoft MSDN library.

No value given for one or more required parameters.

When you get error like 

Error: 0xC002F210 at yourtaskname  task 1, Execute SQL Task: Executing the query "
..." failed with the following error: "No value given for one or more required parameters.". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.

Reason is less number of Variables in Parameter Mapping then used parameter markers in SQL Statement. 

For example you defined only one variable for OLE DB and your SQL command wants two: 

SELECT FirstName, LastName, Title FROM Person.Contact WHERE ContactID = ? AND FirstName = ?

Multiple-step OLE DB operation generated errors. Check each OLE DB status value, if available. No work was done.

Even you used proper parameter names and corresponding  count of variables and parameter makers in SQL statment. You can stil get error like:

Error: 0xC002F210 at yourtaskname  task 1 1, Execute SQL Task: Executing the query "
..." failed with the following error: "Multiple-step OLE DB operation generated errors. Check each OLE DB status value, if available. No work was done.". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.

Reason should be GO used between more SQL statements. After GO are variables cleared, so parameter markers are no more assigned to variables 

For example, if you assign 1 variable as parameter and you SQL is like:

SELECT 1
GO
SELECT [BusinessEntityID] ,[PersonType] ,[FirstName] ,[LastName]

 FROM [Person].[Person] WHERE PersonType = ?

You must remove GO, or split step to more Execute SQL Tasks.

Tweet