Trying to load an excel sheet into a table having 3 columns. Two of its
column are composite keys. Getting an error on the load transformCopy:
Destination does not allow NULL on colum Pair....

appreciate any help!

-TAsh

Loading from excel can be full of problems unless you use the OpenRowSet
syntax. I think this problem lies with the way DTS interperates the
spreadsheet. On many occasions I use to try and import a spreadsheet with
1000 rows in it but DTS cept trying to import 1010 rows and the last 10 were
all blank. I turned out that the user had entered data into the last ten rows
and then deleted the data. DTS read this as a valid record.

I would suggest the following. Import the spreadsheet into a new table and
check the row count. If it is importing null rows use the OPENROWSET syntax.

eg.

INSERT INTO DestTable
SELECT F4 AS Customer
, F5 AS LVL
FROM OPENROWSET ('Microsoft.Jet.OLEDB.4.0','Excel 8.0; HDR=NO;
IMEX=1;Database=<FileLocation>', 'SELECT * FROM [sheet1$] where F4 is not
null ')

Is it possible to load duplicate records into a composite key field?
Appreciate any suggestion. Thanks!


"HWUK" wrote:

Sure thing, as long as it's not a primary key or unique.