April 30, 2013 at 3:51 pm
Hi I found this script for saving all DTS pkges.
Can someone help me in sacving all the DTS t0 H:\DTS location
--------------------------------------------------------------
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[s_SavePackages]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[s_SavePackages]
GO
Create procedure s_SavePackages
@Pathvarchar(128)
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
frommsdb..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
May 4, 2013 at 8:51 am
Are you sure you are looking for help with extracting DTS Packages (SQL 2000 technology) from SQL 2008? DTS Packages can be stored in msdb in SQL 2008 but it is a rare setup these days.
Are you maybe looking for help with SSIS Packages stored in msdb instead?
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply