Importing data from excel to sql server
I am trying to import data into sql server using ssis packages. I have a column named description and it has a cell that has 690 characters.
I am getting a truncation error because SSIS cannot determine the required maximum length. I tried to change the properties of the output column from the original excel source but it didn't help. However, when I move the cell with the maximum length to the first line, it works fine (since the ssis package determines the maximum length from the first 8 lines).
Since I want to automate this process, I don't want to modify the excel sheets every time. I read about changing the number of lines ssis reads but couldn't figure out how to do it.
Any help would be appreciated.
source to share
The answer to the question is that you go to the windows registry and change the excel connection manager to view more than 8 rows to determine the type and length of the column data, which solves the problem.
Go to the Windows Registry by typing "regedit" in the Startup 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.
source to share
You need to specify the length of the input columns of the Excel source code.
In your SSIS package, right click the Excel source in the data flow task and click Advance Editor for Excel Source. Then change the data type and column length to the default.
If you are using VS2015 or above, right click on Excel properties and click. At the bottom of the clic properties in the link Show Advance Editor
and change the length in the tab Input and Output Properties
.
Hope it works for you.
source to share