Creating a DTS Package to Copy Access Database to SQL 2000
===

I need to create a DTS Package that copies the data from an Access file to out SQL Server 2000 database.

Access Database location:
> \\Fileserver1\HR\Employees.mdb

Access Tables: 

- EmployeeInfo
- MGRSPVSR
- ShiftInfo

SQL Server 2000 Database location:
> Production

This DTS job needs to be run hourly.

Human Resourses uses an old program that stores the data to this Access database, and the job is needed to make the data available on the SQL server.

Currently, we have an old DTS job that copies one (1) of the tables over: `EmployeeInfo`.

No one that works here now wrote created that job, and no one knows how to edit it to include the other tables or write another DTS job to include the other tables.

One of the developers here knows enough about SQL to tell me that the picture below actually defines the current DTS job, but he does not know how to go about creating a new one or editing the one we have now.

![Existing DTS Package Screenshot][1]

What the existing job appears to do is deletes the `EmployeeInfo` table from the SQL Server, then copies the `EmployeeInfo` table from the Access file into the SQL server's database.

There does not appear to be any error checking, either. If the Access database file were to mysteriously vanish, the table on the SQL Server would be deleted and never repopulated. (if I am looking at this correctly)

I'd like to have 1 job that first checks that the Access database is there and that it is able to copy the data from the three (3) data tables, **then** drop the corresponding tables from the SQL Server and insert the Access data into it.

Can anyone provide me with some help?


  [1]: http://i.stack.imgur.com/jQIKy.png