SQL作业执行包失败，但VS不执行

发布于 2025-01-05 21:48:42 字数 1363 浏览 1 评论 0原文

当我直接从 Visual Studio 运行包时，它不会抛出错误。当我通过 SQL Server 代理作业执行包时，它会抛出错误：

来源：Fact_Invoice_Item PWBConsolidation [1] 说明：SSIS 错误代码 DTS_E_OLEDBERROR。发生 OLE DB 错误。错误代码：0x80004005。 OLE DB 记录可用。来源：《微软 SQL Server Native Client 10.0" Hresult: 0x00040EDA 描述: “警告：空值会被聚合或其他 SET 消除操作。”。结束错误错误：2012-02-14 07:17:49.01 代码： 0xC0047038 来源：Fact_Invoice_Item SSIS.Pipeline
说明：SSIS 错误代码 DTS_E_PRIMEOUTPUTFAILED。主要输出组件“PWBConsolidation”上的方法 (1) 返回错误代码 0xC0202009。当管道运行时，组件返回失败代码引擎称为 PrimeOutput()。故障码的含义是由组件定义，但错误是致命的并且管道停止执行。在此之前可能已发布错误消息有关失败的更多信息。结束错误 DTExec：包执行返回 DTSER_FAILURE (1)。开始时间：上午 7:14:35 完成时间：上午 7:17:50 已用时间：195.094 秒。套餐执行失败。注意：该步骤已重试了请求的次数次（3）但没有成功。该步骤失败。

我已经隔离了导致问题的部分。在 Ole Db Source 中，我使用查询来获取数据，当我删除以下行时，包将通过 Sql Server Agent Job 成功执行：

Checksum = Checksum(S.BrokerID, S.TeamID, II.DatabaseName, II.INVOICE, I.Invoice_Date, P.Expiration_Date, P.Effective_Date, 
    P.Binder_Effective, I.AGENCY, II.MARKET, P.POLICY, CT.LINE_OF_BUSINESS, II.Coverage_Type, SUB.INSURED, 
    LOB.Description, CT.Description, I.Date_Due, I.Installment, P.Installments, P.ENDORSES, I.Inv_Tdate, Inv_Acctcur,
    INS.INDUSTRY, SUB.SUBMISSION
    )

编辑：
我刚刚检查过，看起来部分数据通过了数据流，在某些时候它只是抛出错误。我已经手动设置了 Checksum=2 但仍然不起作用

知道为什么会这样吗？

非常感谢，
伊利亚

原文

When I run package directly from Visual Studio then it doesn't throw error. When I execute the package via SQL Server Agent Job then it throws error:

Source: Fact_Invoice_Item PWBConsolidation [1] 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: 0x00040EDA Description:
"Warning: Null value is eliminated by an aggregate or other SET
operation.". End Error Error: 2012-02-14 07:17:49.01 Code:
0xC0047038 Source: Fact_Invoice_Item SSIS.Pipeline
Description: SSIS Error Code DTS_E_PRIMEOUTPUTFAILED. The PrimeOutput
method on component "PWBConsolidation" (1) returned error code
0xC0202009. 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 Error DTExec: The
package execution returned DTSER_FAILURE (1). Started: 7:14:35 AM
Finished: 7:17:50 AM Elapsed: 195.094 seconds. The package
execution failed. NOTE: The step was retried the requested number of
times (3) without succeeding. The step failed.

I have isolated a part that is causing the problem. In Ole Db Source I use query to get the data and when I remove the following line then the package is executed successfully via Sql Server Agent Job:

Checksum = Checksum(S.BrokerID, S.TeamID, II.DatabaseName, II.INVOICE, I.Invoice_Date, P.Expiration_Date, P.Effective_Date, 
    P.Binder_Effective, I.AGENCY, II.MARKET, P.POLICY, CT.LINE_OF_BUSINESS, II.Coverage_Type, SUB.INSURED, 
    LOB.Description, CT.Description, I.Date_Due, I.Installment, P.Installments, P.ENDORSES, I.Inv_Tdate, Inv_Acctcur,
    INS.INDUSTRY, SUB.SUBMISSION
    )

EDIT:
I have just checked and looks like part of data gets through data flow and at some point it just throws error. I have manually set Checksum=2 and still doesn't work

Any idea why is this so?

Thanks a lot,
Ilija

分享到QQ

分享到微博