April 8, 2005 at 9:31 am
Hello friends, one question
in sql200 is possible to save all of DTS ?
can i do using the enterprise manager?
or i need to use a script?
sorry my bad english, thank you.
April 11, 2005 at 8:00 am
This was removed by the editor as SPAM
April 11, 2005 at 8:40 am
All the DTS packages are stored in msdb, so if you backup msdb, the packages are there. If you want file version, you can save them as "structured Storage Files" in the DTS "Save As" dialog.
April 12, 2005 at 7:41 pm
If you want to save all of your DTS Packages to Structured Storage Files.. You may want to try using the following script:
CREATE PROCEDURE [dbo].[xp_DTSExportPackage]
@Path varchar(128) = 'C:\DTSExport\'
AS
set nocount on
declare @objPackage int
declare @PackageName varchar(128)
declare @rc int
declare @ServerName varchar(128)
declare @FileName varchar(128)
declare @FilePath varchar(128)
declare @cmd varchar(2000)
select @ServerName = @@ServerName ,
@FilePath = @Path
if right(@Path,1) <> '\'
begin
select @Path = @Path + '\'
end
-- create output directory - will fail if already exists but ...
select @cmd = 'mkdir "' + @FilePath + '"'
exec master..xp_cmdshell @cmd
create table #packages (PackageName varchar(128))
insert #packages
(PackageName)
select distinct name
from msdb..sysdtspackages
select @PackageName = ''
while @PackageName < (select max(PackageName) from #packages)
begin
select @PackageName = min(PackageName) from #packages where PackageName > @PackageName
select @FileName = @FilePath + @PackageName + '.dts'
exec @rc = sp_OACreate 'DTS.Package', @objPackage output
if @rc <> 0
begin
raiserror('failed to create package rc = %d', 16, -1, @rc)
return
end
exec @rc = sp_OAMethod @objPackage, 'LoadFromSQLServer' , null,
@ServerName = @ServerName, @Flags = 256, @PackageName = @PackageName
if @rc <> 0
begin
raiserror('failed to load package rc = %d, package = %s', 16, -1, @rc, @PackageName)
return
end
-- delete old file
select @cmd = 'del ' + @FileName
exec master..xp_cmdshell @cmd, no_output
exec @rc = sp_OAMethod @objPackage, 'SaveToStorageFile', null, @FileName
if @rc <> 0
begin
raiserror('failed to save package rc = %d, package = %s', 16, -1, @rc, @PackageName)
return
end
exec @rc = sp_OADestroy @objPackage
end
GO
-Mike Gercevich
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply