使用文本列> 8000个字符将CSV导入SQL Server

I'm trying to import a csv file with two columns (sku, description) into SQL Server 2008 using the SQL Server management studio 2012 import/export wizard. Because the description column definitely has rows greater than 8000 characters, I go to the advanced tab when choosing the csv data source and click on the description column and click "Suggest Types". It then puts in 16718 for the OutPutColumnWidth property. Apparently there is a description in there somewhere that is THAT long.

我正在尝试使用SQL Server管理工作室2012导入/导出向导将带有两列(sku,description)的csv文件导入SQL Server 2008。因为描述列肯定有大于8000个字符的行,所以在选择csv数据源时我会转到高级选项卡,然后单击描述列并单击“建议类型”。然后它为OutPutColumnWidth属性输入16718。显然,那里有一个描述很长。

The sql it generates is:

它生成的sql是:

CREATE TABLE [dbo].[mag-prod-descriptions1] (
[sku] varchar(7),
[descrip] varchar(16718)
)

However, when I execute the import, I get the error "Could not connect source component. Error 0xc0204016: SSIS.Pipeline: The "Source - mag-prod-descriptions1_csv.Outputs[Flat File Source Output].Columns[Column 1]" has a length that is not valid. The length must be between 0 and 8000."

但是,当我执行导入时,我收到错误“无法连接源组件。错误0xc0204016:SSIS.Pipeline:”源 - mag-prod-descriptions1_csv.Outputs [平面文件源输出] .Columns [Column 1]“长度无效。长度必须介于0到8000之间。“

If I change the OutputColumnWidth property to 8000 then I get an error saying the column was truncated. I can't win.

如果我将OutputColumnWidth属性更改为8000,那么我会收到一个错误,指出该列已被截断。我不能赢。

How do I get the thing to allow me to import cells that are greater than 8000 characters?

如何让我能够导入超过8000个字符的单元格?

2 个解决方案

#1

Ack. Finally got it. The answer was to use the Text Stream datatype for the large column on the source file.

确认。终于明白了。答案是对源文件中的大列使用Text Stream数据类型。

#2

I see import issues with column size errors, I'm thinking...could this be a Unicode issue? Try using nvarchar instead of varchar when creating your table.

我看到列大小错误的导入问题,我在想......这可能是Unicode问题吗?创建表时尝试使用nvarchar而不是varchar。

Reference: Unicode Works Better With SSIS

参考:Unicode使用SSIS更好地工作

#1