DTS Access -> SQL 7

I need to bulk copy several access tables into SQL 7, completing the tasks in the following order :
Drop SQL Table
Re-create SQL Table
Set some Permissions
Move the data from Access into SQL table
Report Success

BUT, if any task fails I need the table to still exist in the state it was before starting the tasks.  I've got a DTS package that looks like it should work, but if a task fails it refuses to call my SQL command, rollback.   HELP!

In Advance - Thanks.
Who is Participating?

[Webinar] Streamline your web hosting managementRegister Today

tchalkovConnect With a Mentor Commented:
Instead of using DTS Package write your own script which is doing the work. First register the access database as linked server - for example accessdata.
then call BEGIN TRAN
and do all the thing you want to do
and at the end call COMMIT TRAN
you can reference tables in access database by using accessdata...tablename
i.e. select * from accessdata...table1

Victor SpiridonovCommented:
Instead of droping the table, rename it and drop only if import is done without error.
Dazza051197Author Commented:
how do I register my access database as a linked server ?
the easyiest way to do this is by using Enterprise manager - go to your server then choose security, then right click on "linked servers" and choose "New Linked Server" and then follow the instructions of the wizard. If however you want to do this in a script then you can use sp_addlinkedserver and then sp_addlinkedsrvlogin - you can see books online for help about these functions. There is also an example on how to add a linked access database
Dazza051197Author Commented:
Took me a while to sort out the parameters, and then how to select the records (I didn't see the 3 dots you put, duh!). Anyway all sorted now, and I thank you very much for you help.  Hope the extra 50 points come in handy.
All Courses

From novice to tech pro — start learning today.