Hi All

I'm looking for some information on how the 'Copy SQL Server Objects' task
works.

We do a nightly copy of 50+ tables from one database to another. Sometimes,
after we have made some modifications in the source database, the copy
fails.

Say the task fails on table #40, what actually happens after the failure?
Does it stop copying the remaining tables? Does it rollback what it's done?
Does it truncate and load each table individually, or in a batch.

--
Thanks
Phill Carter

I do not use this task very often but here was my test

2 DBs (One and Two)

One contains 4 tables (1,2,3,4)
Two contains 2 tables (1,2)

Copy objects tasks says "Do not create destination objects" "Replace data"
direction is One --> Two

The replace data stage will try to delete from all tables. 3 and 4 do not
exist at the destination so should fail

I enter a row of data in 1 and 2 so I can see if anything happens to these
tables

The task fails because it could not remove data from 3 and 4 but the row of
data makes it into 1 and 2 at Two.

Hope that makes sense.


--
--

Allan Mitchell MCSE,MCDBA, (Microsoft SQL Server MVP)
www.allisonmitchell.com - Expert SQL Server Consultancy.
www.SQLDTS.com - The site for all your DTS needs.
I support PASS - the definitive, global community
for SQL Server professionals - http://www.sqlpass.org


"Phill" <no_reply_pacarter (AT) bellpotter (DOT) com.au> wrote

So would it be safe to say that each destination table truncated and loaded
in turn, and a single failure does not fail the entire copy?


"Allan Mitchell" <allan (AT) no-spam (DOT) sqldts.com> wrote

You can see what it is doing in the progress bar but my very small test
certainly said that what has happenened has happened and is not reversed
out.


--
--

Allan Mitchell MCSE,MCDBA, (Microsoft SQL Server MVP)
www.allisonmitchell.com - Expert SQL Server Consultancy.
www.SQLDTS.com - The site for all your DTS needs.
I support PASS - the definitive, global community
for SQL Server professionals - http://www.sqlpass.org


"Phill" <no_reply_pacarter (AT) bellpotter (DOT) com.au> wrote