How can I run a DTS package from within SQL Server - e.g. a stored-procedure? - 11 May 1999

A. You can either :-

1. Run from xp_cmdshell "dtsrun dtsfile"

or 

2. Use sp_OA sp's. Example of this is below (courtesy of Bill Hodghead)

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

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

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


create proc sp_displayoaerrorinfo
@object as int 
as
Declare @hr int
DECLARE @output varchar(255)
DECLARE @source varchar(255)
DECLARE @description varchar(255)

PRINT 'OLE Automation Error Information'

EXEC @hr = sp_OAGetErrorInfo @object, @source OUT, @description OUT
IF @hr = 0
BEGIN
SELECT @output = ' Source: ' + @source
PRINT @output
SELECT @output = ' Description: ' + @description
PRINT @output
END

ELSE
BEGIN
PRINT ' sp_OAGetErrorInfo failed.'
RETURN
END
GO


create proc sp_displaypkgerrors
@pkg as int 
as
declare @numsteps int
declare @steps int
declare @step int
declare @stepresult int
declare @pkgresult int
declare @hr int

select @pkgresult = 0

EXEC @hr = sp_OAGetProperty @pkg, 'Steps', @steps OUTPUT
IF @hr <> 0
BEGIN
print 'Unable to get steps'
EXEC sp_displayoaerrorinfo @pkg --, @hr
RETURN
END

EXEC @hr = sp_OAGetProperty @steps, 'Count', @numsteps OUTPUT
IF @hr <> 0
BEGIN
print 'Unable to get number of steps'
EXEC sp_displayoaerrorinfo @steps --, @hr
RETURN
END

while @numsteps > 0 
Begin 
EXEC @hr = sp_OAGetProperty @steps, 'Item', @step OUTPUT, @numsteps
IF @hr <> 0
BEGIN
print 'Unable to get step'
EXEC sp_displayoaerrorinfo @steps --, @hr
RETURN
END

EXEC @hr = sp_OAGetProperty @step, 'ExecutionResult', @stepresult OUTPUT
IF @hr <> 0
BEGIN
print 'Unable to get ExecutionResult'
EXEC sp_displayoaerrorinfo @step --, @hr
RETURN
END

select @numsteps = @numsteps - 1
select @pkgresult = @pkgresult + @stepresult
end

if @pkgresult > 0
print 'Package had ' + cast(@pkgresult as varchar) + ' failed step(s)'
else 
print 'Packge Succeeded'

GO


create proc sp_executepackage
@packagename varchar(255), --package name, gets most recent version
@userpwd varchar(255) = Null, --login pwd
@intsecurity bit = 0, --use non-zero to indicate integrated security
@pkgPwd varchar(255) = '' --package password
as
declare @hr int
declare @object int

--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 = HOST_NAME()
declare @flag int
select @flag = 0

if @intsecurity = 0 
if @userpwd = Null
EXEC @hr = sp_OAMethod @object, 'LoadFromSqlServer',NULL, @ServerName=@svr, @ServerUserName=@login, @PackageName=@packagename, @Flags=@flag, @PackagePassword = @pkgPwd
-- ServerName As String, \[ServerUserName As String\], \[ServerPassword As String\], \[Flags As DTSSQLServerStorageFlags = DTSSQLStgFlag_Default\], \[PackagePassword As String\], \[PackageGuid As String\], \[PackageVersionGuid As String\], \[PackageName As String\], \[pVarPersistStgOfHost\])
else
EXEC @hr = sp_OAMethod @object, 'LoadFromSqlServer',NULL, @ServerName=@svr, @ServerUserName=@login, @PackageName=@packagename, @Flags=@flag, @PackagePassword = @pkgPwd, @ServerPassword = @userpwd
else
begin
select @flag = 256
EXEC @hr = sp_OAMethod @object, 'LoadFromSqlServer',NULL, @ServerName=@svr, @PackageName=@packagename, @Flags=@flag, @PackagePassword = @pkgPwd
end

IF @hr <> 0
BEGIN
print 'LoadFromSQLServer failed'
EXEC sp_displayoaerrorinfo @object --, @hr
RETURN
END

--execute it
EXEC @hr = sp_OAMethod @object, 'Execute'
IF @hr <> 0
BEGIN
print 'Execute failed'
EXEC sp_displayoaerrorinfo @object --, @hr
RETURN
END

--return the step errors as a recordset
exec sp_displaypkgerrors @object

-- unitialize the package
EXEC @hr = sp_OAMethod @object, 'UnInitialize'
IF @hr <> 0
BEGIN
print 'UnInitialize failed'
EXEC sp_displayoaerrorinfo @object --, @hr
RETURN
END

-- release the package object
EXEC @hr = sp_OADestroy @object
if @hr <> 0
Begin
EXEC sp_displayoaerrorinfo @object --, @hr
RETURN
end 
GO