January 13, 2005 at 11:40 am
To all,
I found this script on the internet, however I keep getting an error when running on some servers. Could someone help me. what is wrong with the script? Why am I getting this error?? See script below.
/** ERROR -
Server: Msg 50000, Level 16, State 1, Procedure s_SavePackages, Line 67
failed to save package rc = -2147024773, package = Ceridian -> Concur (DEVELOPMENT)
**/
SCRIPT--
/**
---Save all DTS packages on server to files
This will save all dts packages on the server to storage files.
It uses a trusted connect to access the package - just change the LoadFromSQLServer call to use a sql server connection.
**/
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
@Path varchar(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
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
Nicole Williams
January 14, 2005 at 9:05 am
I think there is a problem with the statement:
select @FileName = @FilePath + @PackageName + '.dts'
if the path does not have the final '\'
Although the \ is added to @Path, it is not added to @FilePath so the statement above does not work.
I moved the statement to follow the check:
if right(@Path,1) '\'
begin
select @Path = @Path + '\'
end
select @ServerName = @@ServerName ,
@FilePath = @Path
and the sql executed (as script in Query Analyzer, I didn't do the proc create).
I now have copies of all the dts packages on the server's C drive in a dts_backup folder. They can be opened and the layout is presevered - which DTSBackup didn't do the last time I looked at it.
Statement reads like this:
set nocount on
declare @Path varchar(128)
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 @Path = 'c:\dts_backup'
if right(@Path,1) '\'
begin
select @Path = @Path + '\'
end
select @ServerName = @@ServerName ,
@FilePath = @Path
-- 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 @PackageName
select @FileName = @FilePath + @PackageName + '.dts'
exec @rc = sp_OACreate 'DTS.Package', @objPackage output
if @rc 0
begin
-- EXEC sp_OAGetErrorInfo @object, @src OUT, @desc OUT
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
January 14, 2005 at 10:47 am
I recommend that you go with pacarter's suggestion of using a solution from http://www.sqldts.com
If you don't want to use the DTSBackup2000 tool, you can transfer them manually using DTS via a DataPump. Check out the article...
http://www.sqldts.com/default.aspx?204
This is a very simple procedure if you use this approach.
January 14, 2005 at 10:48 am
I recommend that you go with pacarter's suggestion of using a solution from http://www.sqldts.com
If you don't want to use the DTSBackup2000 tool, you can transfer them manually using DTS via a DataPump. Check out the article...
http://www.sqldts.com/default.aspx?204
This is a very simple procedure if you use this approach.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply