By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
440,230 Members | 2,427 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 440,230 IT Pros & Developers. It's quick & easy.

Creating a DTS package

P: n/a
I've created a DTS package and now I need to distribute it to
different servers.

I've been looking for a way to automatically/programatically create a
DTS package, but have not found anything definite.

From the DTS package itself, I see where I can save the dts package as
a structured file, with the name XXXXX.dts. Once I have that DTS
file, how dow I turn it back into a dts package in Enterprise manager?
I don't want to have to manually create the package for 300+
servers...

Thanks,
Jennifer
Jul 20 '05 #1
Share this Question
Share on Google+
1 Reply


P: n/a
You can load a DTS structured storage file from EM by right-clicking on
the Data Transformation Services folder and selecting Open Package. You
can then select Package --> Save As in order to save it locally.

This can be a bit tedious if you have a lot of servers. The VBScript
example below will load your DTS structured storage file and save it to
multiple servers using a trusted connection. See the Books Online for
details.

Dim DtsPackage
Set DtsPackage = CreateObject("DTS.Package2")
DtsPackage.LoadFromStorageFile "C:\MyDtsPackages\MyDtsPackage.dts", ""
DtsPackage.SaveToSqlServer "MyServer1", , , 256
DtsPackage.SaveToSqlServer "MyServer2", , , 256
DtsPackage.SaveToSqlServer "MyServer3", , , 256

Note that you can execute a DTS package directly from a structured
storage file using DTSRUN. If your servers have access to a shared
network location, you could execute the package with a UNC path rather
than saving the package locally on each server.

--
Hope this helps.

Dan Guzman
SQL Server MVP

-----------------------
SQL FAQ links (courtesy Neil Pike):

http://www.ntfaq.com/Articles/Index....partmentID=800
http://www.sqlserverfaq.com
http://www.mssqlserver.com/faq
-----------------------

"Jennifer" <je**********@hotmail.com> wrote in message
news:33**************************@posting.google.c om...
I've created a DTS package and now I need to distribute it to
different servers.

I've been looking for a way to automatically/programatically create a
DTS package, but have not found anything definite.

From the DTS package itself, I see where I can save the dts package as
a structured file, with the name XXXXX.dts. Once I have that DTS
file, how dow I turn it back into a dts package in Enterprise manager?
I don't want to have to manually create the package for 300+
servers...

Thanks,
Jennifer

Jul 20 '05 #2

This discussion thread is closed

Replies have been disabled for this discussion.