SSIS 文本被截断，状态值为 4
我正在开发一个 SSIS 包，尝试从 CSV 平面文件更新现有的 SQL 表。除一列外，所有列均已成功更新。如果我在截断时忽略此列，我的包将成功完成。所以我知道这是一个截断问题而不是错误。
此列几乎每一行都是空的。但是，有几行该字段的长度为 200-300 个字符。我的数据转换任务将该字段标识为 DT_WSTR，但从我在其他地方读到的内容来看，这可能应该是 DT_NTEXT。我已经尝试了这两种方法，甚至将 DT_WSTR 设置为 500。但这都没有解决我的问题。我该如何修复？我的 SQL 表中的该列应采用什么数据类型？
Error: 0xC02020A1 at Data Flow Task 1, Source - Berkeley812_csv [1]: Data conversion failed. The data conversion for column "Reason for Delay in Transition" returned status value 4 and status text "Text was truncated or one or more characters had no match in the target code page.".
Error: 0xC020902A at Data Flow Task 1, Source - Berkeley812_csv [1]: The "output column "Reason for Delay in Transition" (110)" failed because truncation occurred, and the truncation row disposition on "output column "Reason for Delay in Transition" (110)" specifies failure on truncation. A truncation error occurred on the specified object of the specified component.
Error: 0xC0202092 at Data Flow Task 1, Source - Berkeley812_csv [1]: An error occurred while processing file "D:\ftproot\LocalUser\RyanDaulton\Documents\Berkeley Demographics\Berkeley812.csv" on data row 758.
I am developing a SSIS package, trying to update an existing SQL table from a CSV flat file. All of the columns are successfully updating except for one column. If I ignore this column on truncate, my package completes successfully. So I know this is a truncate problem and not error.
This column is empty for almost every row. However, there are a few rows where this field is 200-300 characters. My data conversion task identified this field as a DT_WSTR, but from what I've read elsewhere maybe this should be DT_NTEXT. I've tried both and I even set the DT_WSTR to 500. But none of this fixed my problem. How can I fix? What data type should this column be in my SQL table?
Error: 0xC02020A1 at Data Flow Task 1, Source - Berkeley812_csv [1]: Data conversion failed. The data conversion for column "Reason for Delay in Transition" returned status value 4 and status text "Text was truncated or one or more characters had no match in the target code page.".
Error: 0xC020902A at Data Flow Task 1, Source - Berkeley812_csv [1]: The "output column "Reason for Delay in Transition" (110)" failed because truncation occurred, and the truncation row disposition on "output column "Reason for Delay in Transition" (110)" specifies failure on truncation. A truncation error occurred on the specified object of the specified component.
Error: 0xC0202092 at Data Flow Task 1, Source - Berkeley812_csv [1]: An error occurred while processing file "D:\ftproot\LocalUser\RyanDaulton\Documents\Berkeley Demographics\Berkeley812.csv" on data row 758.
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（11）
出现此错误的一个可能原因是您的分隔符（逗号、分号、竖线等）实际上出现在一列的数据中。这可能会给出非常具有误导性的错误消息，通常会使用完全不同的列名称。
检查此问题的一种方法是将“坏”行重定向到单独的文件，然后手动检查它们。以下是如何执行此操作的简要说明：
http:// /redmondmag.com/articles/2010/04/12/log-error-rows-ssis.aspx
如果这确实是您的问题，那么最好的解决方案是修复源文件以引用数据值和/或使用数据中没有的不同分隔符。
One possible reason for this error is that your delimiter character (comma, semi-colon, pipe, whatever) actually appears in the data in one column. This can give very misleading error messages, often with the name of a totally different column.
One way to check this is to redirect the 'bad' rows to a separate file and then inspect them manually. Here's a brief explanation of how to do that:
http://redmondmag.com/articles/2010/04/12/log-error-rows-ssis.aspx
If that is indeed your problem, then the best solution is to fix the files at the source to quote the data values and/or use a different delimeter that isn't in the data.
我以前遇到过这个问题，很可能是文件的默认列大小不正确。它的默认大小为 50 个字符，但您正在使用的数据更大。在数据文件的高级设置中，将列大小从 50 调整为表的列大小。
I've had this issue before, it is likely that the default column size for the file is incorrect. It will put a default size of 50 characters but the data you are working with is larger. In the advanced settings for your data file, adjust the column size from 50 to the table's column size.
我怀疑
或者一个或多个字符在目标代码页中不匹配
错误的一部分。
如果删除该列中包含值的行，它会加载吗？
换句话说，您能否识别导致包失败的行？
可能是数据太长，或者可能是 SQL Server 不喜欢其中的一些时髦字符。
I suspect the
or one or more characters had no match in the target code page
part of the error.
If you remove the rows with values in that column, does it load?
Can you identify, in other words, the rows which cause the package to fail?
It could be the data is too long, or it could be that there's some funky character in there SQL Server doesn't like.
如果这是来自 SQL Server 导入向导，请尝试编辑数据源上列的定义，默认情况下为 50 个字符，但可以更长。
数据源 ->高级->查看出错的列 ->将 OutputColumnWidth 更改为 200，然后重试。If this is coming from SQL Server Import Wizard, try editing the definition of the column on the Data Source, it is 50 characters by default, but it can be longer.
Data Soruce -> Advanced -> Look at the column that goes in error -> change OutputColumnWidth to 200 and try again.我以前遇到过这个问题，您可以转到“选择数据源”页面的“高级”选项卡，然后单击“建议类型”按钮，然后根据需要设置“行数”。之后，限定的类型和文本被设置为真实值。
我应用了上述解决方案，并且可以将我的数据转换为 SQL。
I've had this problem before, you can go to "advanced" tab of "choose a data source" page and click on "suggested types" button, and set the "number of rows" as much as you want. after that, the type and text qualified are set to the true values.
i applied the above solution and can convert my data to SQL.
就我而言，某些行的列数与标题的列数不同。例如，标题有 10 列，其中一行有 8 或 9 列。 （列 = 计算每行中分隔符的数量）
In my case, some of my rows didn't have the same number of columns as the header. Example, Header has 10 columns, and one of your rows has 8 or 9 columns. (Columns = Count number of you delimiter characters in each line)
如果所有其他选项均失败，请尝试重新创建数据导入任务和/或连接管理器。如果自最初创建任务以来您进行了任何更改，这有时会起作用。我知道这相当于重新启动，但是，嘿，如果它有效，它就有效。
If all other options have failed, trying recreating the data import task and/or the connection manager. If you've made any changes since the task was originally created, this can sometimes do the trick. I know it's the equivalent of rebooting, but, hey, if it works, it works.
我有同样的问题，这是由于一列数据很长。
当我映射它时，我将它从 DT_STR 更改为 Text_Stream，并且它有效
I have same problem, and it is due to a column with very long data.
When I map it, I changed it from DT_STR to Text_Stream, and it works
在目标中，提前检查列的长度是否等于源。
In the destination, in advanced, check that the length of the column is equal to the source.
列的OutputColumnWidth 必须增加。
路径：源连接管理器-->高级-->OuputColumnWidth
OuputColumnWidth of column must be increased.
Path: Source Connection manager-->Advanced-->OuputColumnWidth
就我而言，我需要通过指向实际文件然后重置所有列来重置源平面文件定义中的所有列
In my case I was need to reset all columns in defenition of source flat file by pointing the actual file and then resetting all columns