SQL job running SSIS. Failed to complete cursor operation because table schema changed after cursor was declared

Question

I try to solve this problem for a couple of days, but none of the problems when searching the Internet directly explains why I get this error, because the cursor is not running in this package.

I have an SQL job that runs an SSIS package. This package is mainly explained here:

As shown above, there are a number of schema changes that may ultimately explain why I get this error, but this error never occurs when packages are executed manually and only through SQL Job. It seems to happen spontaneously, one day it works, and then it gives me this error:

Microsoft (R) SQL Server Run UtilityVersion 10.50.4000.0 for the 64-bit version of C (C) of Microsoft Corporation 2010. All rights reserved. Start: 04:30:00 AMError: 2016-11-11 04: 31: 35.91 Code: 0xC0202009 Source: loading the static database into the database loading into the stage database [114] Description: SSIS error code DTS_E_OLEDBERROR. An OLE DB error has occurred. Error Code: 0x80004005. An OLE DB record is available. Source: "Microsoft SQL Server Native Client 10.0" Hresult: 0x80004005 Description: "The cursor operation could not be completed because the table schema changed after the cursor was declared." End ErrorError: 2016-11-11 04: 31: 35.92 Code: 0xC0209029 Source: loading the scene loading database into the database with steps [114] Description: SSIS error code DTS_E_INDUCEDTRANSFORMFAILUREONERROR. The input "OLE DB Destination Input" (127) "failed because error code 0xC020907B occurred, and the location of the error line on the" input "OLE DB Destination Input" (127) "indicates an error. An error has occurred in the specified object of the specified component. There may be error messages posted before, with additional information about the failure. And ErrorError: 2016-11-11 04: 31: 35.92 Code: 0xC0047022 Source: Download to the SSIS.Pipeline base database Description: SSIS error code DTS_E_PROCESSINPUTFAILED. The ProcessInput method on the component "Loading into the stage database" (114) failed with error code 0xC0209029 while processing the input "OLE DB Destination Input" (127). The selected component returned an error from the ProcessInput method. The error refers to the component, but the error is fatal and will cause the data flow task to stop working. There may be error messages posted before, with additional information about the failure. And ErrorError: 2016-11-11 04: 31: 36.06 Code: 0xC02020C4 Source: loading the Retrieve ITExtr03FinM 1 stage database Description: an attempt to add a line to the data stream task buffer failed with error code 0xC0047020.End ErrorError: 2016-11-11 04: 31: 36.11 Code: 0xC0047038 Source: loading into the SSIS.Pipeline stage database Description: SSIS error code DTS_E_PRIMEOUTPUTFAILED. The PrimeOutput method on the "Retrieve ITExtr03FinM" component (1) returned error code 0xC02020C4. The component returned a failure code when the pipeline engine was called PrimeOutput (). The failure code value is determined by the component, but the error is fatal and the pipeline has stopped executing. There may be error messages posted before, with additional information about the failure. And ErrorDTExec: DTSER_FAILURE (1) package executed. Started: 04:30:00 AMFinished: 04:31:36 AMElapsed: 95.859 seconds

The above error mentions Load into Stageing Database - this data flow task contains the following:

Can someone explain this anomaly and is there a way around this?

Answer 1

Reading the error I believe that the key to answering your question is not the cursor, but rather these parts of the error:

• The PrimeOutput method on the "Retrieve ITExtr03FinM" component (1) returns error code 0xC02020C4
• DTS_E_PRIMEOUTPUTFAILED
• Loading into Retrieve ITExtr03FinM 1 database assembly Description: An attempt to add a row to the data stream task buffer failed with error code 0xC0047020

This discussion on SqlServerCentral suggests a RAM / memory buffer configuration as a problem https://ask.sqlservercentral.com/questions/46865/ssis-data-flow-task-getting-error-code-0xc02020c4.html

This link suggests a problem with the 32nd and 64-bit versions. Are you using a 64-bit local and 32-bit server? SSIS ERROR: attempt to add a string to the data stream task buffer not with error code 0xC0047020

I found some more links that suggest that the operating system is a problem that will arise due to the fact that this is a problem on one machine, but not another, because the configurations will be different. In addition, if you run the package for a long time using the server, less memory will be available for your operations.

Answer 2

Check the schedule of other SQL agents and maintenance tasks on your server, in particular around 4:30. The most consistent time during which your package fails is 4:30 in the morning, and I bet that it updates or updates statistics, or another such package works simultaneously with a conflict with your package.

Replication may also occur, which also contradicts your package.