Friday, March 23, 2012

rundts from within a stored procedure

I saved a dts package as a structured storage file and put it in a directory.

I'm trying to call and run it with the following stored procedure-

CREATE PROCEDURE run_dts AS

DECLARE @.object int
DECLARE @.hr int

--create a package object
EXEC @.hr = sp_OACreate 'DTS.Package', @.object OUTPUT
if @.hr <> 0
BEGIN
print 'error create DTS.Package'
RETURN
END

EXEC @.hr = sp_OAMethod @.object, 'LoadFromStorageFile',
NULL, '\\server\directory\billing.dts', ''
IF @.hr <> 0
BEGIN
print 'error LoadFromStorageFile'
RETURN
END

EXEC @.hr = sp_OAMethod @.object, 'Execute'
IF @.hr <> 0
BEGIN
print 'Execute failed'
RETURN
END
GO

If I run this from query analyzer, it gives me "The command(s) completed successfully," but the dts doesn't run?

Any ideas?try checking the permission context\owner of the the stored procedure vs. that of the DTS package.

No comments:

Post a Comment