SSIS Error: Could not complete cursor operation

1
votes

I have a SQL Server Job that runs each morning. It exists in an environment that I do not have access to.

This job runs several SSIS packages. The first package it runs is rather simple. It has 5 basic steps:

  1. Execute SQL. Creates a staging table into which the flat file source is inserted.
  2. Data Flow Task. Moves the data from the flat file source to the OLEDB Destination (staging table). I have one Data Conversion task which simply sets every column to nvarchar(255). This works fine.
  3. Sequence Container (3 Execute SQL Tasks inside). This container holds three Execute SQL Tasks, all of which do data updates to the staging table (Updates NULLS, invalid text, etc).

I do not use a cursor in the SSIS package itself. I have listed the error below:

An OLE DB record is available. Source: "Microsoft OLE DB Provider for SQL Server" Hresult: 0x80004005 Description: "Could not complete cursor operation because the table schema changed after the cursor was declared.". End Error

This error also references two components:

OLE DB Destination [25] Description: SSIS Error Code DTS_E_INDUCEDTRANSFORMFAILUREONERROR. The "input "OLE DB Destination Input" (38)" failed because error code 0xC020907B occurred, and the error row disposition on "input "OLE DB Destination Input" (38)" specifies failure on error.

I am not building or using a cursor in this SSIS package. The only database changes I am making is building the staging table.

The contact I am working with to troubleshoot this said it could be caused by NULL values, however, all of my columns are NULLable.

3
sql-serversql-server-2008ssis
The cursor must be contained in code that gets called, directly or indirectly, during package execution. Without seeing that code, there's nothing anyone on SO can do to help debug it. - Tab Alleman
I was afraid of that. However, I suppose it's enough at this point to rule out the package itself. I'm currently the only member of this project, so I appreciate being able to at least bounce stuff off of SO. If we need to close this, we can. - Phoenix

3 Answers

0
votes

Obviously SSIS uses cursors internally to handle some of your required tasks - its quite common for MS developers to use cursors. You can try to isolate what task is erroring out by running your project and disabling a task and running it or by reading the progress tab very carefully.

I would try, for the Execute SQL Tasks, turn Bypass Prepare to true and try the tasks again.

0
votes

It turns out that the environment was patched in such a way where the database indices needed rebuilt. As a result of that, the schema was different than the stored metadata when the package was executed.

I am sure there is more than one cause of this error, but this is what happened for us.

Thanks to all those who commented.

0
votes

We had the same error message when running a package which uses a SCD component for a dimension table. Following the suggestion of rebuilding the indexes worked for us. However we executed two actions. To be exact, we did the following:

  1. dropped and recreated the indexes
  2. recreated the SCD component of the dimension package (simply change the type of one non-business key attribute to a business-key attribute, complete wizard and vice versa).