本文介绍了SSIS - 间歇性错误“转换失败,因为数据值溢出指定类型."的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!

问题描述

我有一个 SSIS 包,它间歇性地失败并出现一些错误,其中两个最有用的是:

I have an SSIS package which intermittently fails with a handful of errors, the two most informative of which are:

DFT_PlaySummarySSIS 错误代码 DTS_E_OLEDBERROR.发生 OLE DB 错误.错误代码:0x80004005.OLE DB 记录可用.来源:Microsoft SQL Server Native Client 10.0" Hresult:0x80004005 描述:无效的日期格式".OLE DB 记录可用.来源:Microsoft SQL Server Native Client 10.0" Hresult:0x80004005 描述:无效的日期格式".**

DFT_PlaySummary输入OLE DB 目标输入"(2121) 上的输入列dtCreated"(2129) 出错.返回的列状态为:转换失败,因为数据值溢出了指定类型.".**

DFT_PlaySummary There was an error with input column "dtCreated" (2129) on input "OLE DB Destination Input" (2121). The column status returned was: "Conversion failed because the data value overflowed the specified type.".**

源数据库和目标数据库都是 SQL Server 2008 R2.源和目标中的 dtCreated 列都是 DATETIME (NOT NULL).

Both source and destination DB's are SQL Server 2008 R2. The column dtCreated in both source and destination is DATETIME (NOT NULL).

现在,我了解错误消息所说的内容的基础,但我无法理解数据或转换如何在包中无效而没有在源头被视为无效.

Now, I understand the basis of what the error message is saying, but I can't understand how the data or the conversion could be invalid in the package without having already been deemed invalid at the source.

奇怪的是,有时包会失败并出现此错误,重新启动(根据包设置)然后在没有任何手动干预的情况下成功.

Strangely, on some days the package will fail with this error, restart itself (as per the package settings) and then succeed without any manual intervention.

关于我应该在可能导致这种情况的包装步骤中寻找什么的任何指示?数据流任务本身由几个 OLE DB 源组成,每个源都有一个派生列,然后是这些数据集的合并,然后根据 dtCompleted 值有条件地拆分到各种 OLE DB 目标.

Any pointers as to what I should be looking for within the package steps that could be causing this? The data flow task itself consists of a couple of OLE DB sources, each with a derived column, followed by a merge of these data sets and then a conditional split to various OLE DB destinations depending on the dtCompleted value.

推荐答案

可以尝试的两个选项,

  1. 在数据流任务中使用数据转换来转换必要的值.
  2. 在派生列本身中设置特定数据类型(如果您的列在那里可用).

当这种类型的错误经常发生时,数据转换很方便.

Data conversion is handy when this type of error occurs commonly.

应正确使用 SSIS 日期时间格式.下面的文章清楚地解释了差异.使用您需要的正确格式.

SSIS date time format should be used correctly. The below article explanins difference clearly. Use the correct format, which is required for you.

http://consultingblogs.emc.com/jamiethomson/archive/2005/11/15/SSIS_3A00_-What_2700_s-the-difference-between-DT_5F00_DATE-and-DT_5F00_DBTIMESTAMP.aspx

这篇关于SSIS - 间歇性错误“转换失败,因为数据值溢出指定类型."的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持!

08-29 02:52
查看更多