Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
dhw
Constraint Violating Yak Guru
332 Posts |
 Posted - 2002-12-03 : 11:57:03
|
| I would like to create a dts package on my local server and then be able to distribute that same package to other servers (with the same database). Is this possible? If so, is there any information or documentation to describe this process?thanks.... |
|
|
M.E.
Aged Yak Warrior
539 Posts |
Posted - 2002-12-03 : 12:04:31
|
Very possible. Go to file -----> Save AsThe save as screen will pop up. In the middle of it (hard to miss) it will ask which server you want to save to. I'm not sure how much info/documentation there is available on the save as command though. BOL?  -----------------------SQL isn't just a hobby, It's an addiction |
 |
|
|
dhw
Constraint Violating Yak Guru
332 Posts |
Posted - 2002-12-03 : 12:09:01
|
| Sorry...I didn't make myself clear. I want to distribute this dts package to another sql server...but that server is not going to be on my domain/network. As part of a updating process for our application, I would like to give the customers my dts package without having to re-create it at every customer.thanks... |
|
|
|
nr
SQLTeam MVY
12543 Posts |
Posted - 2002-12-03 : 12:28:27
|
| This will save a package (well all packages) to a file.You can change the calls to load the package from a file and tell the client to run it.Just change LoadFromSQLServer and SaveToStorageFile to LoadFromStorageFile and SaveToSQLServer.With v2000 you may have to change Package to Pakage2.As this doesn't have any screen ifo it will lose any nice graphical layouts you have.(Of course if you always load dts packages from files then you don't have this problem - just dump them in the directory).you will need to set @Path--this should save a package to the directoryuse msdbset nocount ondeclare @objPackage intdeclare @PackageName varchar(128)declare @rc intdeclare @ServerName varchar(128)declare @out varchar(1000)declare @Path varchar(100)declare @id int select @Path = '\\myserver\c$\dtssave\' select @ServerName = @@ServerName create table #a(s varchar(500), id int identity) insert #a select distinct name from sysdtspackages select @id = 0 while @id < (select max(id) from #a) begin select @id = min(id) from #a where id > @id select @PackageName = s from #a where id = @id select @PackageName exec sp_OACreate 'DTS.Package', @objPackage output exec @rc = sp_OAMethod @objPackage, 'LoadFromSQLServer' , null, @ServerName = @ServerName, @Flags = 256, @PackageName = @PackageName select @out = @Path + @PackageName exec @rc = sp_OAMethod @objPackage, 'SaveToStorageFile' , null, @out exec sp_OADestroy @objPackage end drop table #a==========================================Cursors are useful if you don't know sql.DTS can be used in a similar way.Beer is not cold and it isn't fizzy. |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2002-12-03 : 12:28:30
|
| Then do the "Save As" and save it as a file. The file extension will be dts. |
|
|
|
dhw
Constraint Violating Yak Guru
332 Posts |
Posted - 2002-12-03 : 12:43:27
|
| >>Then do the "Save As" and save it as a file. The file extension will be dts. This might work for me. How do I load that dts file into another sql server? As a quick test, I saved one my packages to .dts and then tried to load it as a new package on another server...but I don't see any options to load the dts package from the file. Is there some setting or menu option for this?thanks.. |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2002-12-03 : 13:01:25
|
| Just right click on "Data Transformation Services" and click open package. Then navigate to your package and open it. |
|
|
|
dhw
Constraint Violating Yak Guru
332 Posts |
Posted - 2002-12-03 : 14:00:26
|
| Thanks! That is perfect. |
|
|
|
|
|
|
|
|
Save DTS Pkg to another SQL Server?