Dear All,

I am trying to start a DTS package from a Store procedure.
It doesn't like the 'LoadFromSQLServer' command. Can
anyone tell me what is wrong ?

Here is the code

DECLARE @hPkg int
DECLARE @hResult int

DECLARE @sPackageName varchar(255)
DECLARE @sServerName varchar(255)
DECLARE @sServerUserName varchar(255)
DECLARE @sServerPassword varchar(255)
DECLARE @sMethod varchar(255)
DECLARE @lFlags int
DECLARE @src varchar(40)
DECLARE @desc varchar(100)

SET @sPackageName = 'A'
SET @sServerName = 'INVESTMENTS1'
SET @sMethod = ''
SET @sServerUserName = ''
SET @sServerPassword = ''
SET @lFlags = 256

EXEC @hResult = sp_OACreate 'DTS.Package', @hPkg OUT
if @hResult <> 0
Print 'Cannot Create'
else
Print 'Created'

SET @sMethod = 'LoadFromSQLServer'
EXEC @hResult = sp_OAMethod @hPkg, @sMethod, NULL,
@sServerName, @sServerUserName, @sServerPassword,
@lFlags, 'A'

IF @hResult <> 0
BEGIN
EXEC sp_OAGetErrorInfo NULL, @src OUT, @desc OUT
SELECT Info = 'Method - '+@sMethod, Source = @src,
Description = @desc
END
ELSE
Print 'It Worked'

EXEC @hResult = sp_OADestroy @hPkg

Thanks
J

I would better add schedule to the package from SP.
Have a look at msdb..sp_add_jobschedule / msdb..sp_delete_jobschedule /
msdb..sp_help_jobschedule
HTH

Slav.


"Julie" <anonymous (AT) discussions (DOT) microsoft.com> wrote

Have you seen this?

Execute a package from T-SQL
(http://www.sqldts.com/default.aspx?210)

--

Allan Mitchell (Microsoft SQL Server MVP)
MCSE,MCDBA
www.SQLDTS.com
I support PASS - the definitive, global community
for SQL Server professionals - http://www.sqlpass.org


"Julie" <anonymous (AT) discussions (DOT) microsoft.com> wrote