I' m using setup program for making my project's install.exe. How can I
create my DTS packages from command or query if is possible.

Haki wrote:
You can create packages through the (COM) package object library. A
simple example can be generated by using the Save As VB option in
Enterprise Manager.

A simpler method would to just save the packages as structured storage
files (.dts) and include them in your project as content files. You can
then load them from disk. Another alternative would be to save them to a
SQL Server as part of the setup process. Again this can be done through
the object model.

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

Ok, I can use this; but if I generate SQL script of
[msdb].[dbo].[sysdtspackages] can I insert it on new SQL Server
[msdb].[dbo].[sysdtspac kages] ? is it work ?

"Darren Green" wrote:

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.


Haki wrote:

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

I mean row not all the table
When I paste row of package I have an error 64k line limit on Query
Analyzer.If I use another Editor it's ok but when I want to enter design
package I have an error "Cannot find specified version of package in the
storeage location specified"

"Darren Green" wrote:

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.

Haki wrote:

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

I want to add DTS package on my database script, my setup program creating
sql server database from script, I know writing code is better than this way
but I have to use script.

"Darren Green" wrote:

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





"Haki" wrote: