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 🙂