Hello,
I have a stored procedure to export DTS packages to files and I'd like to
modify it to do same for my SSIS packages, so I have modified the procedure
like that:
CREATE procedure [dbo].[s_SavePackages_90]

@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


select @cmd = 'mkdir ' + @FilePath

exec master..xp_cmdshell @cmd



create table #packages (PackageName varchar(128))

insert #packages

(PackageName)

select distinct name

from msdb..sysdtspackages90 where description is not NULL


select @PackageName = ''

while @PackageName < (select max(PackageName) from #packages)

begin

select @PackageName = min(PackageName) from #packages where PackageName >
@PackageName

select @FileName = @FilePath + @PackageName + '.dtsx'

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

But it produced the error: failed to load package rc = -2147217900, package
= Package1

Could you give me a hint how to fix the problem?

Thanks,

GB