January 15, 2007 at 6:07 am
I was trying to import a file from excel, but several of the fields were over 255 characters, which causes problems on the import (truncates at 255 characters)
As an alternative I tried exporting the file to csv and importing to that, but no luck. SSIS choked when it found double quotes in a field being imported. Also excel didn't export all the columns if some were blank, eg if I had 10 columns but one particular row only used the first 5, the other 5 were blank sometimes excel wouldn't place the commas for the blank cells, this would lead to SSIS complaining that it couldn't find a column delimiter.
Thus what is the best way to import data from an excel file? Or way to work round these errors?
January 17, 2007 at 8:39 am
You will need several work-arounds in such cases.
One may be that you have to convert some date values when you need to insert them into an SQL Server table. Insert a data conversion component and duplicate or replace the columns with a DB_TIMESTAMP column.
You can edit the datatypes your connection manager is attempting to read from your text (CSV) file in the advanced editor, you cannot do that for Excel connections. You may be able to avoid some problems when the automatic check may fail to determine the type correctly.
You need to take care that your sample input contains a maximum of values, as the string length may not be set correctly otherwise (manual editing in the advainced editor!).
You may be better off reading a complete line at a time and split it into columns inside your package. There are ways to handle quoted text transparently.
Sorry, but IMHO there is no simple answer.
Michael
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply