|
| I have an Excel spreadsheet that I am trying to import data to an SQL 2000
database. One of the input columns has data that seems to me to be
considered numerics and not text. The column is defined as TEXT in the
spreadsheet. But DTS want it as a numeric. When the script receives the
first non-numeric data (text) the DTSSource( "A").Value contains a null,
non-conversion to a number. For example if the spreadsheet contained:
Column: A B
Row 1 : 1080 Cats
Row 2 : 1227 Dogs
Row 3 : __07 Fish
Row 4 : __09 Ant
When I look at the Transform Data Task Properties, Preview..., View Data
display I see the following
A B
1080 Cats
1227 Dogs
Fish
Ant
The column A test data is lost when the data is numeric. Executing an
ActiveX script and looking at the DTSSource( "A") object with the debugger I
see the following:
Record A column: 1080 1227 __07 __09
Value..................... 1080 1227 <null> <null>
ActualSize.............. 8 8 0 0
Attributes............... 118 118 118 118
DefinedSize............ 8 8 8 8
Name.................... "A" "A" "A" "A"
Type..................... 5 5 5 5
Precision............... 15 15 15 15
NumericScale........ 255 255 255 255
OriginalValue........ 1080 1227 <null> <null>
UnderlyingValue.... 1080 1227 <null> <null>
Status.................. 0 0 3 3
Now I don't want to modify the input spreadsheet because I am receiving it
as input from another department. I would like to process it as received
without losing data, so the questions are:
1) What is going on?
2) It lookslike the DTS process it trying to be helpful by convering the
data to a number/numeric. Is this correct?
3) How do I just get the data as a string? No conversion.
4) It is interesting the the actual data in the debugger is also null, why?
I would thought it would be the actual data.
-- JM
|
|