Execute DTS Package from a stored procedure?

When I run the code microsoft give to launch a DTS package from a stored procedure, the procedure runs continuously, never ending.

When I run the DTS package manually, or from a dtsrun utility, it only takes seconds.

I'm trying to automate this package so it will run after a field in a table has been updated.

Any suggestions are more than welcome.

PS. Here's the code I've been using (that doesn't seem to work):

--- Declare Variables
Declare @hr int
Declare @oPkg int

--- Create Package Object
Exec @hr = sp_OACreate 'DTS.Package', @oPkg OUT
If @hr <> 0
Begin
Print &#39;*** Create Package Object Failed&#39;
Exec sp_oageterrorinfo @oPkg, @hr
Return
End

--- Load Package
---DTSSQLStorageFlags :
---DTSSQLFlag_Default = 0
---DTSSQLStgFlag_UseTrustedConnection = 256

Exec @hr = sp_OAMEthod @oPkg,&#39;LoadFromSqlServer(&#34;JENNSERVER&#34;, &#34;&#34;, &#34;&#34;, 256, , , , &#34;RunMS&#34;)&#39;,null
If @hr <> 0
Begin
Print &#39;*** Load Package Failed&#39;
Exec sp_oageterrorinfo @oPkg, @hr
Return
End

--- Execute Package
Exec @hr = sp_OAMethod @oPkg, &#39;Execute&#39;
If @hr <> 0
Begin
Print &#39;*** Execute Failed&#39;
Exec sp_oageterrorinfo @oPkg, @hr
Return
End

--- Clean Up Package
Exec @hr = sp_oadestroy @oPkg
If @hr <> 0
Begin
Print &#39;*** Destroy Package Failed&#39;
Exec sp_oageterrorinfo @oPkg, @hr
Return
End

The best way that I have found to do this is to create a job for the package with no schedule then, when you fire the trigger (or whatever method you are using) following the update, execute the job using sp_start_job. This may be cheesy but, it works consistently and you get to build in fail notification with the job.

Hope this helps!
------------
Jennifer Alvarez at 6/27/01 3:10:20 PM


When I run the code microsoft give to launch a DTS package from a stored procedure, the procedure runs continuously, never ending.

When I run the DTS package manually, or from a dtsrun utility, it only takes seconds.

I&#39;m trying to automate this package so it will run after a field in a table has been updated.

Any suggestions are more than welcome.

PS. Here&#39;s the code I&#39;ve been using (that doesn&#39;t seem to work):

--- Declare Variables
Declare @hr int
Declare @oPkg int

--- Create Package Object
Exec @hr = sp_OACreate &#39;DTS.Package&#39;, @oPkg OUT
If @hr <> 0
Begin
Print &#39;*** Create Package Object Failed&#39;
Exec sp_oageterrorinfo @oPkg, @hr
Return
End

--- Load Package
---DTSSQLStorageFlags :
---DTSSQLFlag_Default = 0
---DTSSQLStgFlag_UseTrustedConnection = 256

Exec @hr = sp_OAMEthod @oPkg,&#39;LoadFromSqlServer(&#34;JENNSERVER&#34;, &#34;&#34;, &#34;&#34;, 256, , , , &#34;RunMS&#34;)&#39;,null
If @hr <> 0
Begin
Print &#39;*** Load Package Failed&#39;
Exec sp_oageterrorinfo @oPkg, @hr
Return
End

--- Execute Package
Exec @hr = sp_OAMethod @oPkg, &#39;Execute&#39;
If @hr <> 0
Begin
Print &#39;*** Execute Failed&#39;
Exec sp_oageterrorinfo @oPkg, @hr
Return
End

--- Clean Up Package
Exec @hr = sp_oadestroy @oPkg
If @hr <> 0
Begin
Print &#39;*** Destroy Package Failed&#39;
Exec sp_oageterrorinfo @oPkg, @hr
Return
End

exec @temp = sp_OADestroy @pkg_obj -- package object