The DTS operation to do this is relativly simple.

Follow these steps

in the enterprise manager
Data transformation services > local packages > right click > new package
click the excel button to add the excel connection
select the file in the connection properties
ok
click the server icon to add a connection to the sql server
enter the properties for the connection to the sql server
ok
click the transform data task button (arrow pointing to a gear)
click on the excel connection
click on the sql server connection (there should be an line connecting them now)
double click on the connecting line
set the transfomation properties
ok

This should be enough to import the file. Click the play button to test it.


if you want to truncate the table on the sql server first
click the execute sql icon (yellow cylinder with looping arrows)
select the sql server connection
enter 'truncate table tablename' for the sql to run
ok
set the order of operations
select the execute sql task
hold down shift and select the excel connection
in the menu select workflow > on completion

This should work. Just be careful that when you test run the package it will use your local machine to execute it. When you schedule it it will use the sql server to execute it.

Any problems let me know.

Eric


>Hi,
> I have an excel file that I need to import to my SQL server once in a while (on a fixed basis,like once a week). The excel file itself is update whenver needed, and everytime we updated, we simply need to wipe out the whole SQL table and replace it with the current Excel table.
>Is there a way to creat a "routine" that runs automatically on a regular basis that wipe out data from the table (ZAPing it in Foxpro term) and import the excel file into the SQL server.
> I am not familiar with stored procedure though.