SSIS 无法转换，因为可能会丢失数据 [英] SSIS cannot convert because a potential loss of data

问题描述

我有一个从 Excel 文档导入到数据库表的 SSIS 包.我收到以下错误:

I have an SSIS package that imports from an Excel doc to a Database table. I am getting the following errors:

[Excel Source [1]] Error: There was an error with output column "ShipTo" (47) on output
"Excel Source Output" (9). The column status returned was: "The value could not be 
converted because of a potential loss of data.".

[Excel Source [1]] Error: SSIS Error Code DTS_E_INDUCEDTRANSFORMFAILUREONERROR.  The
"output column "ShipTo" (47)" failed because error code 0xC0209072 occurred, and the
error row disposition on "output column "ShipTo" (47)" 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.

[SSIS.Pipeline] Error: SSIS Error Code DTS_E_PRIMEOUTPUTFAILED.  The PrimeOutput method
on component "Excel Source" (1) returned error code 0xC0209029.  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.

我已经阅读并找到了确保我的数据类型匹配的文章，并且我已经做到了.在 Excel 文档中，有 184 行，在这些错误 (shipto) 中列出的列中，它们都具有相同的值，即全部".我上次运行时遇到了这个问题，但只有 4 行有All"，所以我只是手动输入这些值，但这一次效率不高.

I have done reading and found articles to ensure that my datatypes match, and I have done that. In the Excel doc, there are 184 rows, and in the column that is listed in these errors (shipto) they all have the same value and that is "All". I ran into this last time I ran it, but it was only 4 rows that had "All" so I just entered those values manually, but that won't be very efficient this time.

在目标表上，ShipTo 列是一个 Varchar(20)，所以我认为大小没有问题，因为它是三件事之一:1 位数字、2 位数字或单词 All.

On the destination table, the column ShipTo is a Varchar(20) so I don't see the size being a problem being that it is one of three things: 1 digit number, 2 digit number, or the word All.

有没有人有任何我可以尝试的其他想法?任何帮助是极大的赞赏.感谢任何可以提供任何帮助的人.

Does anyone have any additional ideas that I would be able to try? Any help is greatly appreciated. Thanks to anyone that can offer any help.

推荐答案

当您第一次设置这个包时，我猜想一个一位或两位数字是 ShipTo 列中的第一个值.您从 Excel 中读取的包为该输入字段选择了一个数字类型，并且ALL"一词使包失败，因为该字段的输入规范是数字.有几种方法可以事先解决这个问题，但要事后解决，最简单的方法是右键单击 Excel 源并选择显示高级编辑器...从那里，选择显示输入和输出属性的选项卡.在该对话框的输入和输出部分的最顶部，找到 ShipTo 列.您必须深入了解才能找到它.将 DataType 设置为string [DT_STR]"，将长度设置为 20.

When you first set up this package, I am guessing that either a one or two digit number was the first value in the ShipTo column. Your package reading from the Excel picked a numeric type for that input field and the word "ALL" fails the package since the input spec for that field is numeric. There are several ways to fix this beforehand, but to fix it after the fact, the easiest way is to right click the Excel Source and choose Show Advanced Editor... From there, choose the tab that says Input and Output Properties. In the topmost part of the inputs and outputs section of that dialog box, find the column ShipTo. You will have to drill down to find it. Set the DataType to "string [DT_STR]" and the length to 20.

单击确定"，然后再次尝试运行您的包.

Click OK then attempt to run your package again.

这篇关于SSIS 无法转换，因为可能会丢失数据的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！