Importing Error - Text was truncated...

Question

Importing Error - Text was truncated...

Viewing 4 posts - 16 through 18 (of 18 total)

You must be logged in to reply to this topic. Login to reply

Jeff Moden SSC Guru Points: 1004312 More actions · Answer 1

VegasL (1/23/2016)
So I tried with and without malformed data, with and without the regedit changes, and basically all I did was [font="Arial Black"]I added a dummy row of data [/font]in the columns that were initially effected, put some really long data in the xls file, then imported with no problem. SQL Server automatically put nvarchar(max).
Thanks to everyone else as well! Really appreciate it!!!!

That might be good for a test but I thought you didn't want to have to change data before importing the data. It would seem that the RegEdit solution would be the best if you're regularly going to have data in excess of 255 characters.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

VegasL Hall of Fame Points: 3006 More actions · Answer 2

You're right Jeff modifying the registry is the best solution since you don't have data integrity issue then.

On a side note, I'm still not understanding why even if column in excel is larger than 255, why in sql when changing to nvarchar(max) doesn't fix issue, any why ignore truncation global error in wizard, why those two functions don't resolve issue (even ignore), i would have thought it would have skipped the row causing error, and moved on..but its obvious not.

Orlando Colamatteo SSC Guru Points: 182279 More actions · Answer 3

VegasL (1/23/2016)
On a side note, I'm still not understanding why even if column in excel is larger than 255, why in sql when changing to nvarchar(max) doesn't fix issue,

Because the problem happens before it hits SQL Server. It happens because the driver thinks the column is 255 max due to having read the first 8 rows and inferring 255. When importing it encounters a row with more than 255 characters and that it where the truncatio. error occurs, while trying to load data from Excel into the SSIS buffer.

any why ignore truncation global error in wizard, why those two functions don't resolve issue (even ignore), i would have thought it would have skipped the row causing error, and moved on..but its obvious not.

Again, it is not getting this far. Those settings are for mapping to the destination. Say you had mapped a text column that was 255 in Excel to a column in your table that was a VARCHAR(200). If the data was all readable from Excel, i.e. 255 or less, then no errors on read. Now when it tries to insert that data into the table any rows 201-255 characters would typically fail because the column is 200 but with the options you mentioned enabled truncation on insert is allowed.

There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato

Jacob Wilkins One Orange Chip Points: 27976 More actions · Answer 4

VegasL (1/23/2016)
You're right Jeff modifying the registry is the best solution since you don't have data integrity issue then.
On a side note, I'm still not understanding why even if column in excel is larger than 255, why in sql when changing to nvarchar(max) doesn't fix issue, any why ignore truncation global error in wizard, why those two functions don't resolve issue (even ignore), i would have thought it would have skipped the row causing error, and moved on..but its obvious not.

+1 to Orlando's explanation.

To say a little more about it (well, not really more, but different, at least), remember that when doing this there are two datatypes to keep in mind for each column. One defined for the source, and one defined for the destination.

Your destination types were fine in this case, but the source types, which are not under your direct control with an Excel source, were wrong. You can see this (but can't do anything about it) in the Column Mappings dialog:

The trick is that with an Excel source you can control the destination types,but you cannot directly control the data type picked for the source. The data type for the source is determined by reading the first N rows (the default value for N is 8); for string data, if those first 8 rows do not contain strings longer than 255 characters, the source type is assumed to be of length 255.

The fact that the destination column in the table is MAX or a length greater than 255 doesn't matter, because as Orlando said, it never gets that far because the data type defined for the source is incorrect.

That's why to get around it you have to either put a string longer than 255 characters in the first 8 rows, or change that registry value so it reads more rows to determine the source type.

It's a bit annoying that they do it that way with an Excel source (with a flat file source you get to choose the source data types yourself; that was what my initial instructions were for, when I thought you were using a flat file source), but that's the wonderful world of Microsoft 🙂