# Database Discussions > MS SQL Server 7/MS SQL Server 2000 >  Execute DTS Package from a stored procedure?

## Jennifer Alvarez

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 :Wink: &#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

----------


## David

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 :Wink: &#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

----------


## shourya_mehra

exec @temp = sp_OADestroy @pkg_obj -- package object

----------

