This will load the dts package from structured storage file @FileName and save to sql server (msdb) as @PackageName.


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