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'sDT_STR
and the output column width can be set to255
. ForVARCHAR(MAX)
it'sDT_TEXT
.So, on the Data Source selection, in the
Advanced
tab, change the data type of any offending columns fromDT_STR
toDT_TEXT
(You can select multiple columns and change them all at once).