Executing a DTS package within a stored procedure

Hi,
The title says it all really - is it possible to execute a DTS package from
within a stored procedure?
Thanks!
Paul1) Schedule the DTS Package as a job, then start the job with sp_startjob.
To start the job you must either be symin or the owner of the job.
2) Use EXEC xp_cmdshell 'dtsrun.exe /N "your_package" /S your_server /E"
Jacco Schalkwijk
SQL Server MVP
"Paul" <Paul@.discussions.microsoft.com> wrote in message
news:BC2756CF-E336-4FF3-9120-610ED42BDDFF@.microsoft.com...
> Hi,
> The title says it all really - is it possible to execute a DTS package
> from
> within a stored procedure?
> Thanks!
> Paul|||Paul
CREATE PROC myDTS
AS
declare @.packagename varchar(255) --package name, gets most recent version
declare @.userpwd varchar(255) --login pwd
declare @.intsecurity bit --use non-zero to indicate integrated security
declare @.pkgPwd varchar(255) --package password
declare @.hr int
declare @.object int
set @.packagename='testing1'
set @.userpwd=null
set @.pkgPwd=''
set @.intsecurity=0
--create a package object
EXEC @.hr = sp_OACreate 'DTS.Package', @.object OUTPUT
if @.hr <> 0
Begin
EXEC sp_displayoaerrorinfo @.object --, @.hr
RETURN
end
--load the package (ADD integrated security support)
declare @.svr varchar(15)
declare @.login varchar(15)
select @.login = SUSER_NAME()
select @.svr = @.@.servername
declare @.flag int
select @.flag = 256
EXEC @.hr = sp_OAMethod @.object,
'LoadFromSqlServer',NULL, @.ServerName=@.svr, @.ServerUserName=@.login,
@.PackageName=@.packagename, @.Flags=@.flag, @.PackagePassword = @.pkgPwd
print @.hr
EXEC @.hr = sp_OAMethod @.object, 'Execute'
IF @.hr <> 0
BEGIN
print 'Execute failed'
EXEC sp_displayoaerrorinfo @.object --, @.hr
RETURN
END
"Paul" <Paul@.discussions.microsoft.com> wrote in message
news:BC2756CF-E336-4FF3-9120-610ED42BDDFF@.microsoft.com...
> Hi,
> The title says it all really - is it possible to execute a DTS package
> from
> within a stored procedure?
> Thanks!
> Paul|||Perfect! Thank you for your time!
Paul
"Jacco Schalkwijk" wrote:

> 1) Schedule the DTS Package as a job, then start the job with sp_startjob.
> To start the job you must either be symin or the owner of the job.
> 2) Use EXEC xp_cmdshell 'dtsrun.exe /N "your_package" /S your_server /E"
> --
> Jacco Schalkwijk
> SQL Server MVP
>
> "Paul" <Paul@.discussions.microsoft.com> wrote in message
> news:BC2756CF-E336-4FF3-9120-610ED42BDDFF@.microsoft.com...
>
>|||Thanks - a little more involved than I was after but still great to know!
Thanks for your time.
Paul
"Uri Dimant" wrote:

> Paul
> CREATE PROC myDTS
> AS
> declare @.packagename varchar(255) --package name, gets most recent versio
n
> declare @.userpwd varchar(255) --login pwd
> declare @.intsecurity bit --use non-zero to indicate integrated security
> declare @.pkgPwd varchar(255) --package password
> declare @.hr int
> declare @.object int
> set @.packagename='testing1'
> set @.userpwd=null
> set @.pkgPwd=''
> set @.intsecurity=0
> --create a package object
> EXEC @.hr = sp_OACreate 'DTS.Package', @.object OUTPUT
> if @.hr <> 0
> Begin
> EXEC sp_displayoaerrorinfo @.object --, @.hr
> RETURN
> end
> --load the package (ADD integrated security support)
> declare @.svr varchar(15)
> declare @.login varchar(15)
> select @.login = SUSER_NAME()
> select @.svr = @.@.servername
> declare @.flag int
> select @.flag = 256
>
> EXEC @.hr = sp_OAMethod @.object,
> 'LoadFromSqlServer',NULL, @.ServerName=@.svr, @.ServerUserName=@.login,
> @.PackageName=@.packagename, @.Flags=@.flag, @.PackagePassword = @.pkgPwd
> print @.hr
> EXEC @.hr = sp_OAMethod @.object, 'Execute'
> IF @.hr <> 0
> BEGIN
> print 'Execute failed'
> EXEC sp_displayoaerrorinfo @.object --, @.hr
> RETURN
> END
>
>
>
> "Paul" <Paul@.discussions.microsoft.com> wrote in message
> news:BC2756CF-E336-4FF3-9120-610ED42BDDFF@.microsoft.com...
>
>