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?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Victor SpiridonovCommented:
Instead of droping the table, rename it and drop only if import is done without error.
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


Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
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.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.