Hi all !

I had a hard time to build a dts package which imports an excel sheet into a
table inside sql server. The columns in the sheet hold mixed data ( numeric
values and text ) and I struggled which estimation of the datatypes done
excel and/or the jet driver ( I don't know ). To ake a long story short, I
decided to convert everything to text, import it into a temporary table
which has only varchaar columns and do the conversion inside sql server by
myself. I've executed my package and wow everything looks fine. I came back
the other day and done it a second time and know what ? It didn't work,
someone tried to be intelligent again and mixed up the datatypes ( the main
problem is the decimal seperator ',' resp. '.' ) . I'd change nothing ! I
thought. Well there was one small difference in my testing scenario, in the
first place I had Excel open with the worksheet loaded and in the second it
was closed. Meanwhile I cross checked it and there's defefinitely a
difference regarding to an open Excel ( the sheet needn't to be open as
veryfied a second ago ). Looks like the Jet driver is behaving differently
regarding to Excels state. Does anybody experienced the same ? Has an
explanation ? Or advice what's the better way ? I am really confused about
this behaviour and so is my customer....

TIA
Meinhard

hi,
I suggest you to use the DataPump (I am not sure what you are using) for
transfeering data from excel to sql. In the properties of the package, use
disconnected edit and add the following to the extended properties of the
Excel Data connection to "IMEX=1"
That means the data source is a mixed one (strings and numerals).
About the second issue, (sheet closed or open), if you use the DataPump
Task, then you can select the sheet which one you want to use and you can map
the columns, it will be easy and visual.

I HTH

Sreenath

"Meinhard Schnoor-Matriciani" wrote:

Hi Sreenath,

thx for your answer but, no it doesn't help. I forgot to mention that I
already use the IMEX=1 param and I'd set ImportMixedType to Text as well as
TypeGuessRows to 1 and the first row ( after the column names
FirstRowHasNames is set to 1 ) in the sheet are definitely strings. Anyway,
it doesn't help, if Excel is open the cells are interpreted as strings (
which is what I want ), if not something is still 'guessing','thinking'
whatever, unfortunately the worng way ( well not always but in 50% the
result are not what I expect ).

Any other ideas ?
Meinhard