"Cursor operation conflict" error while inserting data from flat file source to OLEDB destination
I was working on one of the issue related with SSIS OLEDB destination editor.
Here is the scenario.
We are fetching the record from the excel file and inserting into the SQL through SSIS by using flat file source and OLEDB destination.
While running the package we are getting the following error message.
Error:
SSIS Error Code DTS_E_OLEDBERROR. An OLE DB error has occurred. Error code: 0x80040E23.
An OLE DB record is available. Source: "Microsoft SQL Server Native Client 10.0" Hresult: 0x80040E23 Description:
"Cursor operation conflict".
To avoid the above error in SSIS follow the following steps as a workaround.
Basically following are the two workaround.
1. From SSIS Side.
If you are using the Oledb destination in the SSIS then please follow following steps to avoid the above error.
Open the Oledb Destination editor.
In the Data access mode select the Table or View-fast load option instead of Table or View.
Note: If you are using the SQL destination there is no option of selecting the data access mode as by default the SQL destination is using the bulk insert.
2. From SQL Side.
If you don’t want to change the value of the Data access mode in the OLEDB destination
Then in that case follow following steps.
Go to the SQL server properties windows from the SQL server management studio
Check if the NO COUNT option is selected in the Default connection option.
If that value is checked please unchecked that.
Run the package and test.
Reference article:
What SET NO COUNT basically does?
When SET NOCOUNT is ON, the count is not returned. When SET NOCOUNT is OFF, the count is returned.
The @@ROWCOUNT function is updated even when SET NOCOUNT is ON.
SET NOCOUNT ON prevents the sending of DONE_IN_PROC messages to the client for each statement in a stored procedure. For stored procedures that contain several statements that do not return much actual data, or for procedures that contain Transact-SQL loops, setting SET NOCOUNT to ON can provide a significant performance boost, because network traffic is greatly reduced.
In our scenario the issue seems to be because of the following reason:
The provider is opening the cursor to insert the record and the cursors basically rely on the number of affected rows which is returned after the execution of SQL statements. If the Set NoCount is ON in the connection then it suppresses those messages and because of this there is some conflict in the cursor itself.
Author : Mukesh(MSFT) SQL Developer Engineer, Microsoft
Reviewed by : Debarchan(MSFT) SQL Developer Engineer, Microsoft