Does anyone know what to do about an Excel Null Problem?
I have a DTS Package that uses an excel file as a data
source. Because I have over 800 fields I list the data
into 3 fields. SheetName, FieldName, FieldData. The
problem is when I import the data The Data Driven Query
Task imports certain fields "FieldData" as Nulls. The
DTS package doesn't seem to like mixed values in the same
column. An Example would be for the first 5 rows;
Test,1,2,3,4. The "Test" field would be imported as a
null because the Data Driven query saw that the rest of
the data is numeric. Is there anything I can do when I
import the data? I tried using "Cast(Fieldname as
Varchar) as [FieldName], but it only gave a syntax error.
I can seperate each of the different data types into
different columns, so that the Data Driven Query only
sees one distinct DataType for each column, but because
they are only 3-D References in Excel, the user can
type.. what ever they want into each cell. (Hence the
problem in a nutshell) The user can mis-arrange data
types in my summary sheet.
Thanks for your help, I would greatly appreciate any