The table sysdtspackages will aleeady exist on any SQL 7.0/2000 server.
You can insert packages directly into the table with a bit of
preparation and effort. The simplest way is to use the object model and
the SaveToSQLServer method. You can alternatively use any data transfer
technology, such as DTS itself to insert rows into that table as well.
You will need the supporting meta-data for the columns, and the final
imge column contains the package itself.


[quoted text, click to view]


--
Darren
http://www.sqldts.com

Sounds like you corrupted the image data, or at least it does not match
the other columns. I would not expect copy and paste to be very reliable
for this type of procedure. I would write some code to do this.

[quoted text, click to view]


--
Darren
http://www.sqldts.com

I found it :

DTS Load To File procedure :

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


DTS Load From File procedure :

if exists (select * from dbo.sysobjects where id =
object_id(N'[dbo].[s_LoadPackageToServer]') and OBJECTPROPERTY(id,
N'IsProcedure') = 1)
drop procedure [dbo].[s_LoadPackageToServer]
GO

Create procedure s_LoadPackageToServer
@PackageName varchar(128) ,
@FileName varchar(500) ,
@Username varchar(100) ,
@Password varchar(100)
as
/*
exec s_LoadPackageToServer
@PackageName = 'mypackage' ,
@FileName = 'c:\dtspckgs\mypackage.dts' ,
@Username = 'sa' ,
@Password = 'pwd'
*/
declare @objPackage int
declare @rc int

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, 'LoadFromStorageFile' , null,
@UncFile = @FileName, @password = null
if @rc <> 0
begin
raiserror('failed to load package rc = %d, package = %s', 16, -1, @rc,
@PackageName)
return
end

exec @rc = sp_OAMethod @objPackage, 'SaveToSQLServerAs' , null,
@NewName = @PackageName, @ServerName = @@ServerName, @ServerUserName =
@Username, @ServerPassword = @Password
if @rc <> 0
begin
raiserror('failed to load package rc = %d, package = %s', 16, -1, @rc,
@PackageName)
return
end
go





[quoted text, click to view]