导入 CSV 文件时 SQLServer 中的错误，尽管每列都使用 varchar (MAX)

我试图将一个大的 CSV 文件(几个 G)插入到 SQL Server中，但是一旦我通过 Import Wizard并最终尝试导入该文件，就会得到以下错误报告:

  • 执行(错误) 信息

错误0xc02020a1: 数据流任务1: 数据转换失败 “ Title”列的转换返回状态值4和状态 “文本被截断或一个或多个字符在 目标代码页。”。

(SQL Server Import and Export Wizard)

错误0xc020902a: 数据流任务1: “ Source- Train _ csv. Output [平面文件源输出] . Columns [“ Title”]”失败 因为发生了截断，并且截断行在 “ Source-Train _ csv. Output [平面文件源输出] . Columns [“ Title”] 指定截断时失败。截尾误差发生在 指定组件的指定对象。

(SQL Server Import and Export Wizard)

错误0xc0202092: 数据流任务1: 处理时发生错误 数据行2上的文件“ C: Train.csv”。

(SQL Server Import and Export Wizard)

错误0xc0047038: 数据流任务1: SSIS 错误代码 DTS _ E _ PRIMEOUTPUTFAILED. Source-Train _ csv 上的 PrimeOutput 方法 返回的错误代码0xC0202092。组件返回了一个错误代码 当管道引擎调用 PrimeOutput ()时 错误代码由组件定义，但错误是致命的，并且 管道停止执行。可能发布了错误消息 在此之前有更多关于失败的信息。

(SQL Server Import and Export Wizard)

我创建了一个表来将文件插入到第一个表中，并将每个列设置为保存 varchar (MAX) ，因此我不明白为什么仍然存在这个截断问题。我做错了什么？

194117 次浏览
小开

I think its a bug, please apply the workaround and then try again: http://support.microsoft.com/kb/281517.

Also, go into Advanced tab, and confirm if Target columns length is Varchar(max).

小开
最佳答案

In SQL Server Import and Export Wizard you can adjust the source data types in the Advanced tab (these become the data types of the output if creating a new table, but otherwise are just used for handling the source data).

The data types are annoyingly different than those in MS SQL, instead of VARCHAR(255) it's DT_STR and the output column width can be set to 255. For VARCHAR(MAX) it's DT_TEXT.

So, on the Data Source selection, in the Advanced tab, change the data type of any offending columns from DT_STR to DT_TEXT (You can select multiple columns and change them all at once).

Import and Export Wizard - Data Source - Advanced

小开

The Advanced Editor did not resolve my issue, instead I was forced to edit dtsx-file through notepad (or your favorite text/xml editor) and manually replace values in attributes to

length="0" dataType="nText" (I'm using unicode)

Always make a backup of the dtsx-file before you edit in text/xml mode.

Running SQL Server 2008 R2

小开

Goto Advanced tab----> data type of column---> Here change data type from DT_STR to DT_TEXT and column width 255. Now you can check it will work perfectly.

小开

Issue: The Jet OLE DB provider reads a registry key to determine how many rows are to be read to guess the type of the source column. By default, the value for this key is 8. Hence, the provider scans the first 8 rows of the source data to determine the data types for the columns. If any field looks like text and the length of data is more than 255 characters, the column is typed as a memo field. So, if there is no data with a length greater than 255 characters in the first 8 rows of the source, Jet cannot accurately determine the nature of the data type. As the first 8 row length of data in the exported sheet is less than 255 its considering the source length as VARCHAR(255) and unable to read data from the column having more length.

Fix: The solution is just to sort the comment column in descending order. In 2012 onwards we can update the values in Advance tab in the Import wizard.

小开

This answer may not apply universally, but it fixed the occurrence of this error I was encountering when importing a small text file. The flat file provider was importing based on fixed 50-character text columns in the source, which was incorrect. No amount of remapping the destination columns affected the issue.

To solve the issue, in the "Choose a Data Source" for the flat-file provider, after selecting the file, a "Suggest Types.." button appears beneath the input column list. After hitting this button, even if no changes were made to the enusing dialog, the Flat File provider then re-queried the source .csv file and then correctly determined the lengths of the fields in the source file.

Once this was done, the import proceeded with no further issues.


提交答案