Excel Source dt_ntext problems

Hi:

I import data from multiple excel files into SQL DB. I have trouble with fields that could contain >255 chars.

If I have the col type = DT_Ntext in my Data Flow, the package fails for files that do not have any values >255 chars.

If I have the external coltype=dt_wstr and the output coltype=dt_wstr(4000) the package fails if the file contains any value >255 chars.(Implicit conversion does not occur, as expected).

I worked around by adding a dummy first row with >255 chars.

Is there a way to use a cast function to solve this prob? I tried using Select dt_ntext(fieldname) from Sheet1$, but that does not work.

Is there some clean way to get around this problem?

TIA
Kar

Hi Karfast

For now this have to be done manually. I was able successfully execute getting advise from Bob Bojanic

Follw these instructions carefully:

1) Go to Mapping option at Destination, Point your mouse for each of row having your above problem and its corresponding destination (note the Datatypes types and Lengths)

2) Open Advanced Editor for the Excel Source, Under Amend the Types and Lengths accordingly for all the columns in destination to match your source Columns' type and length

Thanks

Subhash Subramanyam

|||

Metadata of Excel sheets is often hard to deal with. The dummy first row seems as a clever solution to me. It is not elegant but the entire JET provider is far from that.

Select dt_ntext(fieldname) is not going to work as dt_ntext can be recognized only by SSIS and not by the JET provider. The more adequate query would be select LongText(fieldname), but I have no idea if JET can deal with it (don't have a way to try it at the moment).

HTH.

|||

Thanks, Bob.

I tried:

Select LongText(Fieldname) , and also tried out Memo etc. All these give a Undefined Function error.

I cant even find any help on this. There is apparently something called a Jet SQL Reference, but I couldnt find it. Not even in Access 2000 Local Help.

So I guess the dirty workaround is the only way :-(

Kar