Execute SQL Task Error: 0xC002F210, Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly

2-sql-servers3 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

Connection type

Parameter marker

Parameter name

Example SQL command

ADO

?

Param1, Param2, …

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

ADO.NET

@<parameter name>

@<parameter name>

SELECT FirstName, LastName, Title FROM Person.Contact WHERE ContactID = @parmContactID

ODBC

?

1, 2, 3, …

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

EXCEL and OLE DB

?

0, 1, 2, 3, …

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

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.

 


Add comment

Security code
Refresh

Twitter