SQL Server Import wizard fails with incomprehensible message

Question

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).

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.

Can anyone suggest what is wrong here?

Answer 1

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.

Answer 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).

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.

Look into the metadata in the Data Source's "Advanced" tab to resolve the problem.