Without all the details, it's hard to say anything definitively, but you're on the right track asking if Office has to be on the server. The server needs the proper provider software and if you had Office on the server, it would be there, but that's not necessary. You can instead install the Office 12.0 engine or (for older Excel) the "Jet" provider (see link below).

If it is an xlsx (Excel 2007) file, you're NOT connecting to the type of Excel that SQL 2000 knows about, and you need to establish an OLEDB connection. The "Server" connection has an optional drop-down from which you can select a new connect of "Microsoft Office 12.0 Access Database Engine OLE DB Provider". If you have Office on your machine, you'll see this, but you also have to make sure the provider is on the server where the package will run. Configure it under the "Properties" button with the file name in the Connection tab and Extended Properties on the "All" tab as "Excel 12.0".