Execute DTS from a Procedure

[noni]

Hello,

I am trying to run a DTS Package from a stored procedure. But when I execute the procedure
from the Query Analyzer, the procedure never completes. Here is the code for executing the
DTS package.

--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, 'LoadFromSQLServer("myDB","","",256,,,, "OVCF")',
NULL
IF @hr <> 0
BEGIN
print 'error LoadFromStorageFile'
RETURN
END

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

If I comment out the last part (Execute of the object), the procedure runs fine. I need your help
in figuring out what I need to do.

Thanks

[rmiao]

The other way around is creating sql job to run dts package then you starting job in sp.

[noni]

Can you give me the code to run the sql job in a procedure.

[rmiao]

msdb..sp_start_job

[noni]

I am running the msdb..sp_start_job @job_name = 'OF'.
I get the message that the DTS is running, but I am not getting
the any results from the DTS. If I try to run the DTS package
manually, I get the error that the package is running.

Is there a way I can get a return if there is an error? Do you have any examples where this has worked?

Thanks

[rmiao]

Sounds issue in your package. Enable package logging to find out what's wrong first.

[noni]

I am able to run the DTS package and it works fine. It is when I try to run the Scheduled Job that nothing happens.

Do I need additional permissions to run the sql job?

Thanks

[rmiao]

What does the package do? If remote files or so involved, ensure sql service accounts have permission to access them.