0

I'm using SSIS to import an Excel table into SQL Server. The field in the SQL Server table is set as nvarchar(max) but it still gives me Truncate Error. The column that I want to import can have any number of characters, it could be 1 or it could be 10,000. It's a free-text filed without any limitations.

enter image description here enter image description here

jmich738
  • 1,565
  • 3
  • 24
  • 41

2 Answers2

1

Go into the Advanced settings of your Excel Source Component, and manually set the length of the Output columns.

Tab Alleman
  • 31,483
  • 7
  • 36
  • 52
0

SSIS samples your data to get an idea of each column. It will use the max length of the sample to determine the "proper" field size. Of course this causes constant issues.

Can you add something to order your data to make the longest first?

ORDER BY LEN(LongFIELD) DESC

Check out StackExchange for more info: Text was truncated or one or more characters had no match in the target code page When importing from Excel file

Community
  • 1
  • 1
Hannover Fist
  • 10,393
  • 1
  • 18
  • 39
  • Unfortunately I have other tables that have multiple columns with large strings. The other solution I thought of was to convert all excel extracts into CSVs, so that way I can use a Flat File import and set the size myself. Except I can't figure out how to do that with SSIS. – jmich738 Nov 12 '15 at 01:08