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.
我正在开发一个SSIS包，试图从CSV平面文件更新现有的SQL表。除一列外，所有列都已成功更新。如果我在truncate上忽略这一列，我的包就会成功完成。所以我知道这是一个截断问题，而不是错误。
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?
此列几乎每行都为空。但是，有几行，此字段为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.
6 个解决方案
#1
10
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不喜欢的那种时髦的角色。
#2
21
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
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.
如果这确实是您的问题，那么最好的解决方案是在源处修复文件以引用数据值和/或使用不在数据中的不同分隔符。
#3
13
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.
我以前遇到过这个问题，可能是文件的默认列大小不正确。它将默认大小设置为50个字符，但您使用的数据更大。在数据文件的高级设置中，将列大小从50调整为表的列大小。
#4
4
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.
如果这是来自SQL Server导入向导，请尝试编辑数据源上列的定义，默认情况下为50个字符，但可以更长。
Data Soruce -> Advanced -> Look at the column that goes in error -> change OutputColumnWidth to 200 and try again.
Data Soruce - > Advanced - >查看出错的列 - >将OutputColumnWidth更改为200并再试一次。
#5
1
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)
在我的例子中，我的一些行没有与标题相同的列数。例如，Header有10列，其中一行有8或9列。 （列=每行中分隔符字符的计数）
#6
0
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.
如果所有其他选项都失败，请尝试重新创建数据导入任务和/或连接管理器。如果您在最初创建任务后进行了任何更改，这有时可以解决问题。我知道这相当于重启，但是，嘿，如果它有效，它就可以了。
#1
10
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不喜欢的那种时髦的角色。
#2
21
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
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.
如果这确实是您的问题，那么最好的解决方案是在源处修复文件以引用数据值和/或使用不在数据中的不同分隔符。
#3
13
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.
我以前遇到过这个问题，可能是文件的默认列大小不正确。它将默认大小设置为50个字符，但您使用的数据更大。在数据文件的高级设置中，将列大小从50调整为表的列大小。
#4
4
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.
如果这是来自SQL Server导入向导，请尝试编辑数据源上列的定义，默认情况下为50个字符，但可以更长。
Data Soruce -> Advanced -> Look at the column that goes in error -> change OutputColumnWidth to 200 and try again.
Data Soruce - > Advanced - >查看出错的列 - >将OutputColumnWidth更改为200并再试一次。
#5
1
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)
在我的例子中，我的一些行没有与标题相同的列数。例如，Header有10列，其中一行有8或9列。 （列=每行中分隔符字符的计数）
#6
0
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.
如果所有其他选项都失败，请尝试重新创建数据导入任务和/或连接管理器。如果您在最初创建任务后进行了任何更改，这有时可以解决问题。我知道这相当于重启，但是，嘿，如果它有效，它就可以了。