I need to set up our system to do a massive copy (about 300-400) files.
I need to both be able to run all the copies at night, as well as the
option of choosing a file to copy during the day.

This is going to copy from a foreign database to Sql Server 2000.

This was going to be done as both a scheduled copy (at night) and from a
web page.

My problem is I can set up a bunch of packages, each as doing a copy,
but it will take forever to set the packages up. I can't just say move
all the tables, as that wouldn't take into account the option to choose
one file to copy - which would necessitate a package for each file.

Is there an easy way to accomplish this in DTS?

Thanks,

Tom.

So by files you mean database entities or you mean text files?
I presume the former.

You want to copy ALL tables across every night or as needed but also want to
include the option to single out an individual table and bring it across.

It sounds like you also want to bring across ALL data EVERY time.

The best way I can see to do this would be to

Create a package that includes every table
You will need to include an ExecuteSQL task that removes the data from the
destination also (This needs to go first in the package)

You can fire this and it will clear down the tables then reload them

Now you want to know about loading specific tables.

You use the DTS object model and you search through the package looking for
the step that has as the destination the table you want to load. You
disable ALL other steps, except the ExecuteSQL task. You then change
through code the SQLStatement property to only empty your one table.




--
--

Allan Mitchell MCSE,MCDBA, (Microsoft SQL Server MVP)
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


"Thomas Scheiderich" <tfs (AT) deltanet (DOT) com> wrote

Allan Mitchell wrote:


Correct on the database entities.

I actually found out that I only need about 30 tables that need to be
transfered to Sql Server (much more manageable number). So I just
created 30 packages that have your generic Drop Table, Create Table, 2
connections and the data pump - as well as an extra executeSQL task to
add in the indexes for the particular table.

I also have a master package that just has all the packages in them with
no precedence constraints on them. I just let DTS decide which 4
packages to do first. I figured that if I did them in parallel, I could
end up with 3 of the paths finished and having the last path having 4 or
5 packages to go (which would slow the whole process down).

The problem would be that I do need one task to go first. What I
thought I could do is add another task that is called if I am only doing
1 package and pass name of the package into the master package via a
Global variable. If I am running all of the tables, I would just run
the master package, which would have the Global name set to a default value.

To do what you suggest, which sounds like the way to go, would I use an
ActiveX task from the master task to disable all the packages, then
enable the task that want to run? If that is the case, how would I know
what all the tables are and what property would I disable (or enable) to
set this up?

Thanks,

Tom

Yes you could use a Master/Child setup.

OK

Each DataPump task will have two properties of interest to you

SourceObjectName and DestinationObjectName



--
--

Allan Mitchell MCSE,MCDBA, (Microsoft SQL Server MVP)
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


"Thomas Scheiderich" <tfs (AT) deltanet (DOT) com> wrote

Allan Mitchell wrote:

But if I have a package that has, for example, 10 packages and they are
not connected in any way - just run in any order that DTS wants to, how
would I find what they are in my activeX code. Is there some property
list I can look through to find out what they are?

And then what would I use to disable and enable them.

Thanks,

Tom.

So your master package calls 10 Execute Package tasks.?

Your Master will have to

Look at each Execute Package Task - Grab the name of the package.
Open it up (Active Script Task + Object Model)
Look at DataPumps.
It will then need to either disable or enable that ExecutePackage task.


Alternately.

You could read from a database table that was of the format

CREATE TABLE DTSPackagesToGo(PackageName varchar(255), SourceTable
varchar(255), DestinationTable varchar(255))

You can then query to see which of your packages contains the table(s) you
want.

You can build the logic in then to only execute that one (or if the user
chooses then all of them.

How to loop through a global variable Rowset
(http://www.sqldts.com/default.aspx?298)

--
--

Allan Mitchell MCSE,MCDBA, (Microsoft SQL Server MVP)
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


"Thomas Scheiderich" <tfs (AT) deltanet (DOT) com> wrote