SQL Server 2000

I have a DTS Package that is reading an Excel file and, using a
Transform, populating a SQL table with simple Copy transformations. It
is failing as it reads beyond the last populated row in the Excel
worksheet and thus tries to populate one of the table fields with the
null value it finds there.

The Excel file itself is a more recent format than the latest type
available in DTS (Microsoft Excel 97-2000 in DTS) and I have defined
the default value of 0 in both the First Row and Last Row value of the
Transform Data Task Properties.

I know the actual data is ok as, if I alter these to 1 and 22401 (the
actual number), the package works and the data is loaded.

The issue is that the file will change from week to week and so I
cannot give exact values for the number of rows. The process has to
read all rows available.

What can I do to ensure the process stops at the last row populated?

Cheers

Mark