尽管每列都使用了 varchar(MAX)，但在导入 CSV 文件时 SQL Server 中出现错误
我正在尝试将一个大型 CSV 文件(几个演出)插入 SQL Server，但是一旦我通过 Import Wizard 并最终尝试导入文件我收到以下错误报告:
I'm trying to insert a large CSV file (several gigs) into SQL Server, but once I go through the Import Wizard and finally try to import the file I get the following error report:
- 正在执行(错误)消息
错误 0xc02020a1:数据流任务 1:数据转换失败.数据标题"列的转换返回状态值 4 和状态text "文本被截断或一个或多个字符在目标代码页.".
Error 0xc02020a1: Data Flow Task 1: Data conversion failed. The data conversion for column ""Title"" returned status value 4 and status text "Text was truncated or one or more characters had no match in the target code page.".
(SQL Server 导入导出向导)
错误 0xc020902a:数据流任务 1:源 -Train_csv.Outputs[平面文件源输出].Columns["Title"]"失败因为发生了截断，并且截断行处置"源 - Train_csv.Outputs[平面文件源输出].Columns["Title"]"指定截断失败.发生截断错误指定组件的指定对象.
Error 0xc020902a: Data Flow Task 1: The "Source - Train_csv.Outputs[Flat File Source Output].Columns["Title"]" failed because truncation occurred, and the truncation row disposition on "Source - Train_csv.Outputs[Flat File Source Output].Columns["Title"]" specifies failure on truncation. A truncation error occurred on the specified object of the specified component.
(SQL Server 导入导出向导)
错误 0xc0202092:数据流任务 1:处理时发生错误数据行 2 上的文件C:Train.csv".
Error 0xc0202092: Data Flow Task 1: An error occurred while processing file "C:Train.csv" on data row 2.
(SQL Server 导入导出向导)
错误 0xc0047038:数据流任务 1:SSIS 错误代码DTS_E_PRIMEOUTPUFAILED.Source 上的 PrimeOutput 方法 - Train_csv返回错误代码 0xC0202092.组件返回失败代码当管道引擎调用 PrimeOutput() 时.的意义失败代码由组件定义，但错误是致命的并且管道停止执行.可能会发布错误消息在此之前提供有关失败的更多信息.
Error 0xc0047038: Data Flow Task 1: SSIS Error Code DTS_E_PRIMEOUTPUTFAILED. The PrimeOutput method on Source - Train_csv returned error code 0xC0202092. 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.
(SQL Server 导入导出向导)
我首先创建了将文件插入的表，并将每一列设置为保存 varchar(MAX)，所以我不明白我怎么仍然会遇到这个截断问题.我做错了什么?
I created the table to insert the file into first, and I set each column to hold varchar(MAX), so I don't understand how I can still have this truncation issue. What am I doing wrong?
推荐答案
在 SQL Server 导入和导出向导中，您可以在 Advanced 选项卡中调整源数据类型(这些成为如果创建新表则输出，否则仅用于处理源数据).
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).
数据类型与 MS SQL 中的数据类型不同，而不是 VARCHAR(255) 它是 DT_STR 并且输出列宽可以设置为 255.对于 VARCHAR(MAX)，它是 DT_TEXT.
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.
因此，在数据源选择的Advanced 选项卡中，将任何违规列的数据类型从DT_STR 更改为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).
相关文章