Importing Data from excel into sql server

Question

I am trying to import data into sql server using ssis packages. I have a column named description and that has a cell having 690 characters.

I am getting a truncation error as SSIS is not able to figure out the maximum length required. I tried changing the output column properties from the excel source connection but that didn't help. However, when I move the cell with the maximum length to the first row it works perfectly (as ssis package determines the max length from the first 8 rows).

Since, I want to automate this process I don't want to modify the excel sheet each and every time. I have read about changing the number of rowsets that ssis reads, but haven't been able to figure out how to do it.

Any type of help would be appreciated.

Answer 1

The answer to the question is that you go to windows registry and change the excel connection manager to see more than 8 rows to determine the column data type and length, which solves the issue.

You go to windows registry by typing "regedit" in the run console and when you are in the windows registry you follow

HKEY_LOCAL_MACHINE -> SOFTWARE -> Wow6432Node -> Microsoft -> Jet -> 4.0 ->Engines -> Excel -> and then set the typeguessrows to 0, so that it can parse through the entire file instead of the top 8 rows.