SQL Job executing SSIS - Could not complete cursor operation because the table schema changed after the cursor was declared

Question

I have been trying to get this issue resolved for a couple of days but none of the issues when searching on the web directly explains why I am getting this error as there is no cursor being executed on this package.

I have a SQL Job which executes a SSIS package - This package is pretty much explained here :

As the above shows, there are a number of schema changes that happens which could ultimately explain why I am getting this error but this error never seems to occur when the packages is executed manually and only through the SQL Job. It also seems to happen spontaneously, one day it works, then next it give me this error :

Microsoft (R) SQL Server Execute Package UtilityVersion 10.50.4000.0 for 64-bitCopyright (C) Microsoft Corporation 2010. All rights reserved.Started: 04:30:00 AMError: 2016-11-11 04:31:35.91 Code: 0xC0202009 Source: Load into Stageing Database Load into Stageing 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: "Could not complete cursor operation because the table schema changed after the cursor was declared.".End ErrorError: 2016-11-11 04:31:35.92 Code: 0xC0209029 Source: Load into Stageing Database Load into Stageing Database [114] Description: SSIS Error Code DTS_E_INDUCEDTRANSFORMFAILUREONERROR. The "input "OLE DB Destination Input" (127)" failed because error code 0xC020907B occurred and the error row disposition on "input "OLE DB Destination Input" (127)" specifies failure on error. An error occurred on the specified object of the specified component. There may be error messages posted before this with more information about the failure.End ErrorError: 2016-11-11 04:31:35.92 Code: 0xC0047022 Source: Load into Stageing Database SSIS.Pipeline Description: SSIS Error Code DTS_E_PROCESSINPUTFAILED. The ProcessInput method on component "Load into Stageing Database" (114) failed with error code 0xC0209029 while processing input "OLE DB Destination Input" (127). The identified component returned an error from the ProcessInput method. The error is specific to the component but the error is fatal and will cause the Data Flow task to stop running. There may be error messages posted before this with more information about the failure.End ErrorError: 2016-11-11 04:31:36.06 Code: 0xC02020C4 Source: Load into Stageing Database Retrieve ITExtr03FinM 1 Description: The attempt to add a row to the Data Flow task buffer failed with error code 0xC0047020.End ErrorError: 2016-11-11 04:31:36.11 Code: 0xC0047038 Source: Load into Stageing Database SSIS.Pipeline Description: SSIS Error Code DTS_E_PRIMEOUTPUTFAILED. The PrimeOutput method on component "Retrieve ITExtr03FinM" (1) returned error code 0xC02020C4. The component returned a failure code when the pipeline engine called PrimeOutput(). The meaning of the failure code is defined by the component but the error is fatal and the pipeline stopped executing. There may be error messages posted before this with more information about the failure.End ErrorDTExec: The package execution returned DTSER_FAILURE (1).Started: 04:30:00 AMFinished: 04:31:36 AMElapsed: 95.859 seconds

The above error does mention Load into Stageing Database - the following is contained within that Data Flow Task:

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

Answer 1

Reading through the error I believe the key to answering your question isn't the cursor but rather these parts of the error:

• The PrimeOutput method on component "Retrieve ITExtr03FinM" (1) returned error code 0xC02020C4
• DTS_E_PRIMEOUTPUTFAILED
• Load into Stageing Database Retrieve ITExtr03FinM 1 Description: The attempt to add a row to the Data Flow task buffer failed with error code 0xC0047020

this discussion on SqlServerCentral suggests RAM/Memory Buffer configuration as an issue https://ask.sqlservercentral.com/questions/46865/ssis-data-flow-task-getting-error-code-0xc02020c4.html

this link suggests a 32 vs 64 bit issue. Are you using 64 bit local and 32 bit on your server? SSIS ERROR: The attempt to add a row to the Data Flow task buffer failed with error code 0xC0047020

I found a few more links that suggest RAM as an issue, which would make since why it would be an issue on one machine but not another because configurations would be different. Also if you run package during a heavy use time of the server less memory would be available for your operations.

Answer 2

Check the schedule of other SQL Agent and maintenance tasks on your server, specifically around 4:30 AM. The most consistent time your package is failing is 4:30 AM and I bet there's either a reindex, stats update, or other such package running at the same time conflicting with your package.

There could also be replication occurring that's conflicting with your package as well.