Copy DTSs to a new name

Question

Copy DTSs to a new name

swede

SSCommitted

Points: 1773
More actions
January 29, 2007 at 7:51 am

#98042

I have over 100 DTSs that need to be copied to the same server with a new name. Is there a easy way to do this other than bringing each dts up and resaving it under the new name? All the dts packages are prefixed with DTS4_ needs to be copied to DTS9_CDJ_.

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

Greg Charles SSC-Forever Points: 45403 More actions · Answer 1

The only way I can think to change a lot of package names at once, and I'm not sure I'd want to try it, is to update the name column in sysdtspackages and sysdtspackagelog tables in msdb.

Greg

swede SSCommitted Points: 1773 More actions · Answer 2

Updating the name is not what I want. I need to COPY the DTSs from DTS4_ to DTS9_CDJ_. The key to sysdtspackage is NOT name, but id and version id.

Adrian Nichols-360275 SSCertifiable Points: 7694 More actions · Answer 3

Check out this article:

http://www.dbazine.com/sql/sql-articles/larsen8

It worked for me!!!

Ade

A Freudian Slip is when you say one thing and mean your mother.
For detail-enriched answers, ask detail-enriched questions...[/url]

Glenn Wallis SSC Eights! Points: 811 More actions · Answer 4

I have used DTS Backup2000 to copy the packages to another server without issues. I am sure this will work with the local instance as the target.

See http://www.sqldts.com/272.aspx

Greg Charles SSC-Forever Points: 45403 More actions · Answer 5

I've used DTS Backup2000 also, but I haven't been able to copy a package to a new name.

Greg

Kevin Snow Newbie Points: 5 More actions · Answer 6

I've got some old code I used to move packages with.

It was originally designed to move DTSs across servers...but I've modified it for your name change.

It moves one package (slowly). You can use a script to produce a hundred EXEC statements based on the packages...or modify it to move all packages in one execution of the proc.

I tested it locally and was able to successfully open the package created (I didn't run it)

/*exec dbo.MoveDTS 'LocalServer','DB2 Test Package','newowner'

*/

ALTER proc dbo.MoveDTS (

@sourceserver varchar(40),@dtsname varchar(500),

@owner varchar(30)

@newDTSname varchar(500)

)

as

declare @sql varchar(2000)

declare @dtsid uniqueidentifier

set @sql= 'Select d.*,l.[name] as loginame into ##sysdtspackages from '+ @sourceserver + '.msdb.dbo.sysdtspackages d join '+@sourceserver+'.master.dbo.sysxlogins l on d.owner_sid=l.sid'

execute(@SQL)

Select @dtsid=[id] from ##sysdtspackages where [name] like @dtsname + '%'

set @sql= 'Select * into ##sysdtscategories from '+ @sourceserver + '.msdb.dbo.sysdtscategories where [id]=''' +cast( @dtsid as varchar(2000))+''''

execute(@SQL)

table ##sysdtspackages

--Keys here are that you need a newid() to replace the version ID...and you need to pass a valid SID for the owner.

INSERT INTO msdb.dbo.[sysdtspackages] Select @newDTSname, [id], newid(), [description], [categoryid], [createdate], @owner, [packagedata], (Select sid from master.dbo.sysxlogins where [name]=@owner),0 as packagetype/*, [packagetype]*/ from ##sysdtspackages s where s.id = @dtsid

Drop table ##sysdtscategories

Drop Table ##sysdtspackages

GO

Hope this helps.