Truncation Error when Importing Data SQL Server 2008

0
votes

I'm trying to import data into SQL Server using the 'Import Wizard" and it fails with the error pasted below. I have tried 'Suggest Types' and manually set types to varchar 255. I've also set the global truncation error to ignore but it still stops the import. Is there an easier way to import data into SQL Server?

Executing (Error) Messages Error 0xc02020a1: Data Flow Task 1: Data conversion failed. The data conversion for column "Position" 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 - Vast Tech - Personnel Listing (2)_txt.Outputs[Flat File Source Output].Columns[Position]" failed because truncation occurred, and the truncation row disposition on "Source - Vast Tech - Personnel Listing (2)_txt.Outputs[Flat File Source Output].Columns[Position]" 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 "\SANDBOX\Users\bpadgett\Downloads\Vast Tech - Personnel Listing (2).txt" on data row 511. (SQL Server Import and Export Wizard)

Error 0xc0047038: Data Flow Task 1: SSIS Error Code DTS_E_PRIMEOUTPUTFAILED. The PrimeOutput method on Source - Vast Tech - Personnel Listing (2)_txt 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)

2
sql-server-2008importruntime-error
I would look at data row 511 and see whether there's anything odd about the value in column "Position" for that row. Maybe it's longer than 255 chars (though that seems unlikely) - or maybe you have something like a non-printable character in there. - Ann L.

2 Answers

0
votes

To answer your question, no, there really isn't an easier way to import data into SQL Server. But the import wizard can be reasonably easy, once you get used to it.

Your second paragraph of error says:

the truncation row disposition on "Source - Vast Tech - Personnel Listing (2)_txt.Outputs[Flat File Source Output].Columns[Position]" specifies failure on truncation.

My first question would be: How wide is the "Position" column in the target table? Is it possible that you have a Position value that's too wide for it? (I'm assuming it's a varchar in the target table, but if it isn't, that's probably the source of your problems.)

Next, I'm curious as to what the value is in row 511, where it ran into problems. Is there any possibility that the file you're importing has been corrupted or truncated? You don't mention what the input format is: if it's a CSV file and you aren't using text delimiters, is it possible that one of the fields in that row has a comma in it, thus bumping all the other fields over one column? That could result in a longer field getting pushed into the "Position" field's place.

A quick way to check (if you're using a CSV file) would be to open it in Excel, expand all the columns to the width of their contents, and just glance at row 511. If it's off by one or you genuinely do have a Position value that's over 255 characters, it'll be obvious.

I hope this helps.

0
votes

One solution is setting the same width of your nvarchar value in the context. For example if a field its defined FILED NVARCHAR(450) you need to set up the same OutputColomWidth with 450.

This is the example. ImageExample

For default the wizard model set 50 width in a string types.