September 14, 2006 at 12:59 pm
I created a DTS package on one SQL server. I would like to copy the same DTS pacakge to many other servers in my network. I don't see an obvious way to script the package. Is there a way of doing it?
Thanks...Nali
September 14, 2006 at 1:32 pm
Hello Nali,
You can save it as a .dts file and copy it over to the destination server. First right mouse click on the DTS designer window and select save as option. choose the Structured Storage File in the Location drop down.
Copy it over to the destination server and now open it from Enterprise Manager -> Server -> Data Transformation Services -> Right Mouse Click -> Open Package and now select the location of the package.
After opening the package, now you save the same to this server.
here you go..
Hope this helps you.
Thanks
Lucky
September 14, 2006 at 1:44 pm
Lucky, that will work. However, I have close to 300 servers that I need to push this and possibly more than one DTS package. I was hoping to be able to script the package then auto run the script.....?
September 15, 2006 at 12:32 am
No need to script it out if the package is stored as a Local Package. For SQL Server 2000 you can copy the rows from the MSDB database, I'm not sure about 2005
The query you would use to get specific packages on the source system is this:
SELECT * FROM MSDB.dbo.sysdtspackages
WHERE [name] in ('myPackagename_1','myPackagename_2',...'myPackagename_n')
or
SELECT * FROM MSDB.dbo.sysdtspackages
WHERE [name] like ('myPackagename%')
etc..
Just stick this in a DTS Datapump between the source and target servers or link the servers and do a direct insert.
Remember to do a delete on the target table first...
Note that this will grab the whole history since each row represents a version of the package. You can play around with the CreateDate field to get a specific version of a package.
Hope this helps...
Peet
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply