Sql – Errors in SQL Server while importing CSV file despite varchar(MAX) being used for each column

csvsqlsql server

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:

  • Executing (Error)
    Messages

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 Import and Export Wizard)

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 Import and Export Wizard)

Error 0xc0202092: Data Flow Task 1: An error occurred while processing
file "C:\Train.csv" on data row 2.

(SQL Server Import and Export Wizard)

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 Import and Export Wizard)

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?

Best Answer

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

Related Topic