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.
Friday, March 23, 2012
Subscribe to:
Post Comments (Atom)
No comments:
Post a Comment