sql-server - 执行 SSIS 的 SQL 作业 - 无法完成游标操作，因为声明游标后表架构发生了更改

Question

几天来我一直在尝试解决这个问题，但是在网络上搜索时没有任何问题可以直接解释为什么我收到此错误，因为在此包上没有执行游标。

我有一个执行 SSIS 包的 SQL 作业 - 这个包在这里有很多解释:

如上所示，发生了许多架构更改，这最终可以解释为什么我收到此错误，但当仅通过 SQL 作业手动执行包时，此错误似乎永远不会发生。它似乎也是自发发生的，有一天它起作用了，然后接下来它给了我这个错误:

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

上述错误确实提到加载到暂存数据库 - 该数据流任务中包含以下内容:

有人可以解释一下这个异常现象吗？有办法解决这个问题吗？

Answer 1

仔细阅读错误，我相信回答您问题的关键不是光标，而是错误的这些部分:

• 组件“Retrieve ITEXtr03FinM”(1) 上的 PrimeOutput 方法返回错误代码 0xC02020C4
• DTS_E_PRIMEOUTPUTFAILED
• 加载到暂存数据库中检索 ITExtr03FinM 1 说明:尝试向数据流任务缓冲区添加行失败，错误代码为 0xC0047020

SqlServerCentral 上的此讨论表明 RAM/内存缓冲区配置是一个问题 https://ask.sqlservercentral.com/questions/46865/ssis-data-flow-task-getting-error-code-0xc02020c4.html

此链接表明存在 32 位与 64 位问题。您本地使用的是 64 位，服务器上使用的是 32 位吗？ SSIS ERROR: The attempt to add a row to the Data Flow task buffer failed with error code 0xC0047020

我发现了更多链接，表明 RAM 是一个问题，这将说明为什么它在一台机器上是一个问题，但在另一台机器上却不是问题，因为配置会有所不同。此外，如果您在服务器大量使用期间运行包，可用于操作的内存就会减少。