Hi --

I have a database that I need to export about 20 tables from daily to
another database. This exported database will be backed up and shipped off
to a customer on a daily basis.

What is the best way to handle this via DTS?

For example:

1) From EM, I selected the database and then picked each table to export and
selected my destination database. The only problem with this approach is
that I see that EM creates a Source/Dest and task for each table. One of my
requirements will be to truncate all the tables each day before importing.
I guess using the SQL Server default will be a lot of work???

2) I thought about creating my own package with one source/dest and creating
20+ tasks (one per table) and copying data this way. I figured I can then
have a SQL Task setup to truncate all 20+ tables in one shot??

3) After the DTS task is created, I want one of our Operators to launch
manually. What is the best way to do this from DOS prompt? I know about
DTSRUN. How do I execute a package from this? Does the screen display
progress information? Also, does DTSRUN return an errorlevel if a package
fails so that I can trap it within batch?

Any help would be appreciciated.

If you wanted to truncate the data in the tables you would need to create
your own DTS. The easist thing to do would be go through the steps you do
now for exporting the data instead of running it on the last step save it.
Go in and add a new SQL Task to truncated the tables, but be sure you set
them up in the correct order to run.

You could create a windows program that would call the DTS, but it does not
give any status when it runs, unless you export the DTS to VB and put your
own status bar to let the user know what the status is.

Let me know if you need further help.

Rick


"dm4714" <spam (AT) spam (DOT) net> wrote