I am copying a subset of data from our production
warehouse to a test bed warehouse. I am using Execute
Package Tasks in which I place a Copy SQL Server Object
Task. This task copies a single table's data replacing
the existing data in the test bed table. I also include
logging features so I can tell when the copy started, how
long it took and whether it was successful.

When I palce all the Execute Package Tasks in a single
controling Execute Package Task, set the
MaxConcurrentSteps to 4 and execute the controling task...
some of the individual tasks fail telling me that "a file
on the source server is being used by another user or
process!". Additionally, to error IDs are embedded in the
error message (80004005 and 80070020).

I believe what is happening is that as each Copy SQL
Server Object task is executed, a files on the source side
are created which contain information about the tables,
procedures, views indexes, etc that are to be copied.
Although I can specify directory locations for logging and
error files on the destination server (where the DTS
Packages were created and are executed from) I can not
specify the same on the Source Side. Nor can I specify
that multiple Copy SQL Server Object Tasks are running and
that DTS should allow for multiple packages needing access
to "their own" versions of the work files.

Does someone out there know how I can solve this?

I have thought of using a Data Transformation Task with a
Select * FROM Table (I actually use this for some of the
tables and they do run concurrently) but I'm afraid this
may take longer. Does it use BCP under the covers for
this too. Some of my tables are 300+ Million records and
I am worried I will not be able to accomplish the test bed
rebuild in a single night (I must keep my test bed
sychronized with other database I backup and restore to
the test bed).

Thanks

You are probably correct in your assumptions. I would use DataPump tasks
and scripts. It is unlikely to take longer as I find the task you mention
no the quickest in the toolbox anyway. DataPump tasks give you so much more
flexibility as well.

--

----------------------------
Allan Mitchell (Microsoft SQL Server MVP)
MCSE,MCDBA
www.SQLDTS.com
I support PASS - the definitive, global community
for SQL Server professionals - http://www.sqlpass.org



"Scott" <srankin (AT) coolsavings (DOT) com> wrote