SSIS - EXCEL - datatype

Read more than 255 character data values from Excel File (TypeGuessRows) - SSIS Interview

https://www.youtube.com/watch?v=CptX2JUQNec

excel read 8 rows - decide data type - length

1st row - make comment max length- remove split - 1st row remove 

id  Name comments

0 

regedit:

HKEY_LOCAL_MACHINE

SOFTWARE

Wow6432Node

Microsoft

Jet

4.0

Engines

Excel

TypeGuessRows<-double click (8)

change (0) (it reads whole excel to decide the data type).

---

Not a solution:

Excel in SSIS: How to import a column that may have more than 255 characters when DT_NTEXT causes failures?

http://stackoverflow.com/questions/28199929/excel-in-ssis-how-to-import-a-column-that-may-have-more-than-255-characters-whe

1. Change the datatype to DT_WSTR(4000) in the source.
   The column is supposed to have up to 10,000 characters, so I'm not sure this would be a good idea even if it worked. However, I tried it anyway. This time it gave me a truncation error. I changed the truncation error disposition to "ignore failure" and it loaded the data, but truncated the value to 255 characters. I have verified that the length is 4000 and doesn't get changed when I save the file, but it's still truncating at 255 characters.

---

SSIS - Excel destination does not allow characters more than 255

http://dba.stackexchange.com/questions/73060/ssis-excel-destination-does-not-allow-characters-more-than-255

Option1 : Its tricky one, just copy the 9th row to the top (may be as 1 row).Now try to create your package using import/export wizard. Your ssis will create column with width nvarchar(Max) which will accept upto 2 GB.

Option2: Change the Preparation SQL task query, change the data type as varchar(Max) and modify the excel source ->advanced editor and change the output columns type and length and external column type and length.

---