I just want to import two columns from a flat file into a new table. I have set one column, 'Code', to be varchar(50), and another column, 'Description', to be nvarchar(max).
我只想从平面文件中导入两列到新表中。我将一列“Code”设置为varchar(50)，另一列“Description”设置为nvarchar(max)。
The import fails with the following messages:
导入失败的消息如下:
- Executing (Error)
Messages
Error 0xc02020a1: Data Flow Task 1: Data conversion failed. The data conversion for column "Description" 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 Import and Export Wizard)
Error 0xc020902a: Data Flow Task 1: The "output column "Description" (14)" failed because truncation occurred, and the truncation row disposition on "output column "Description" (14)" specifies failure on truncation. A truncation error occurred on the specified object of the specified component.
(SQL Server Import and Export Wizard)
Error 0xc0202092: Data Flow Task 1: An error occurred while processing file "C:\Users\rinaldo.tempo\Desktop\ICD10_Edition4_CodesAndTitlesAndMetadata_GB_20120401.txt" on data row 3.
(SQL Server Import and Export Wizard)
Error 0xc0047038: Data Flow Task 1: SSIS Error Code DTS_E_PRIMEOUTPUTFAILED. The PrimeOutput method on component "Source - ICD10_Edition4_CodesAndTitlesAndMetadata_GB_20120401_txt" (1) 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 Import and Export Wizard)
The error message suggests to me that data is getting truncated as it's being placed in the 'Description' column, which is of type nvarchar(max)! Having eyeballed the input data, I would say the descriptions are never more than around 2 or 300 characters, so this is out of the question.
错误消息告诉我数据被截断了，因为它被放在“Description”列中，该列的类型是nvarchar(max)!对输入数据进行目测后，我想说描述永远不会超过2或300个字符，所以这是不可能的。
Can anyone suggest what is wrong here?
谁能告诉我这里出了什么问题吗?
4 个解决方案
#1
28
The default size for string columns, in the import, is 50 characters. This truncation happens before data goes to your database. You should adjust this in the first step of the Import Wizard, in the Columns section.
在导入中，字符串列的默认大小是50个字符。这种截断发生在数据进入数据库之前。您应该在导入向导的第一步在Columns部分中进行调整。
#2
2
The error
这个错误
"Text was truncated or one or more characters had no match in the target code page."
“文本被截断，或者一个或多个字符在目标代码页中不匹配。”
may occur EVEN when your source flat file is a Unicode file and your target column is defined as nvarchar(max).
即使您的源平面文件是Unicode文件，并且您的目标列被定义为nvarchar(max)，也可能发生这种情况。
SSIS infers data types in the source file from scanning a limited number of rows and making an educated guess. Due to endlessly repeated attempts to get it to work, it parked the metadata for the data type OutputColumnWidth to 50 characters somewhere along the way, causing truncation internal to the package.
SSIS通过扫描有限的行并进行有根据的猜测来推断源文件中的数据类型。由于不断重复尝试使其工作，它将数据类型OutputColumnWidth的元数据保存到沿途的某个地方，导致包内部的截断。
Look into the metadata in the Data Source's "Advanced" tab to resolve the problem.
查看数据源的“Advanced”选项卡中的元数据以解决问题。
#3
0
you cam also try this .
你也可以试试这个。
select all the nvarchar columns as Dt_NTEXT in the advance TAB and then in the data conversion ,select DT_WSTR (Unicode_String) for the Alias Column for all the Nvarchar data Fields.
在advance选项卡中选择所有nvarchar列作为Dt_NTEXT，然后在数据转换中选择DT_WSTR (Unicode_String)作为所有nvarchar数据字段的别名列。
It worked for me :). Try it
它对我起了作用:)。试一试
#4
0
I got this message as well while trying to load a 275 column table. No matter what I did, I couldn't get the message to go away. Changing one column at a time was really difficult. Fix one, get an error in another. Plus some would not seem to fix.
我在尝试加载275列表时也收到了这条消息。无论我做了什么，我都无法让这个信息消失。每次换一列确实很困难。修正一个，在另一个中出错。另外，有些似乎无法修复。
Then I removed all ":" and "," characters from the tab delimited source file, and it loaded just fine.
然后我从标签分隔的源文件中删除了所有的“:”和“，”字符，它加载得很好。
#1
28
The default size for string columns, in the import, is 50 characters. This truncation happens before data goes to your database. You should adjust this in the first step of the Import Wizard, in the Columns section.
在导入中，字符串列的默认大小是50个字符。这种截断发生在数据进入数据库之前。您应该在导入向导的第一步在Columns部分中进行调整。
#2
2
The error
这个错误
"Text was truncated or one or more characters had no match in the target code page."
“文本被截断，或者一个或多个字符在目标代码页中不匹配。”
may occur EVEN when your source flat file is a Unicode file and your target column is defined as nvarchar(max).
即使您的源平面文件是Unicode文件，并且您的目标列被定义为nvarchar(max)，也可能发生这种情况。
SSIS infers data types in the source file from scanning a limited number of rows and making an educated guess. Due to endlessly repeated attempts to get it to work, it parked the metadata for the data type OutputColumnWidth to 50 characters somewhere along the way, causing truncation internal to the package.
SSIS通过扫描有限的行并进行有根据的猜测来推断源文件中的数据类型。由于不断重复尝试使其工作，它将数据类型OutputColumnWidth的元数据保存到沿途的某个地方，导致包内部的截断。
Look into the metadata in the Data Source's "Advanced" tab to resolve the problem.
查看数据源的“Advanced”选项卡中的元数据以解决问题。
#3
0
you cam also try this .
你也可以试试这个。
select all the nvarchar columns as Dt_NTEXT in the advance TAB and then in the data conversion ,select DT_WSTR (Unicode_String) for the Alias Column for all the Nvarchar data Fields.
在advance选项卡中选择所有nvarchar列作为Dt_NTEXT，然后在数据转换中选择DT_WSTR (Unicode_String)作为所有nvarchar数据字段的别名列。
It worked for me :). Try it
它对我起了作用:)。试一试
#4
0
I got this message as well while trying to load a 275 column table. No matter what I did, I couldn't get the message to go away. Changing one column at a time was really difficult. Fix one, get an error in another. Plus some would not seem to fix.
我在尝试加载275列表时也收到了这条消息。无论我做了什么，我都无法让这个信息消失。每次换一列确实很困难。修正一个，在另一个中出错。另外，有些似乎无法修复。
Then I removed all ":" and "," characters from the tab delimited source file, and it loaded just fine.
然后我从标签分隔的源文件中删除了所有的“:”和“，”字符，它加载得很好。