I am trying to automate the rollout of new objects in my production
environment in such a way in that a DBA does not have to be on hand to add
the objects to the database. I think I have everything covered with the
exception of DTS packages. Is there a way to add these files to the sql
server using some form of scripting?

There are several methods available-

Use VBScript/VB or any other COM compliant language to build the entire
package and save to file or SQL server.
You could also do this in T-SQL via the OLE stored procedures, but it would
be a lot more work.

If the package is built then you can save it between servers manually, or
automate this. You can also copy to and from files.

I often copy packages between two servers, e.g.

Transferring DTS Packages
(http://www.sqldts.com/default.aspx?204)

If you want a file stage then this can also be done, similar to above.

The key to most automated methods is that the sysdtspackages table in msdb
holds the package data, and you can hack it directly.

I use osql in batch files for most automated builds, so one option would be
to prepare the dts data using bcp out to a file. Then use bcp in to put it
into the new server in the batch file with the regular osql calls.
Plenty of options but if you are still unclear, can you be a bit more
specific about what your source and destination storage locations are and
what if any steps you need in between.


--
Darren Green
http://www.sqldts.com


"Sheldon W." <Sheldon W. (AT) discussions (DOT) microsoft.com> wrote

As far as the DTS packages go, my source would be the structured storage file
and the destination would be the sql server I wanted to add it to.

The intention is to eliminate the need to open Enterprise Manager and add
the dts package manually. My .cmd batch file contains a listing of all the
objects I am deploying with osql commands executing my various sql scripts
and all I am hoping for is a method that I can call from this batch to add
the packages to the servers I designate.

"Darren Green" wrote:

In message <A5DAB632-E2D6-4D59-A0C4-0E7BA0E69447 (AT) microsoft (DOT) com>, Sheldon
W. <SheldonW (AT) discussions (DOT) microsoft.com> writes
There are no tools in SQL that will do this, but you could write some
VBScript for example. The DTSBackup 2000 tool on my site would also do
it, or you could even write your own, but either way, you will need to
ship an extra execute able/script file to do the work.

Perhaps a similar way would be to load the package into SQL sever (msdb)
first, then export via bcp. The bcp output could then be shipped with
the scripts and easily loaded into the destination from your batch file
without the need for any extra executable code.


--
Darren Green (SQL Server MVP)
DTS - http://www.sqldts.com

PASS - the definitive, global community for SQL Server professionals
http://www.sqlpass.org