| I have deployed a package on my local sql server. How do i write a stored procedure to execute it. I want an application to call the stored procedure which executes this package which in turn returns some results. I have dont this by deploying package on filesys and using dtexec.exe command to execute it. But now I want to do it from a stored procedure. |
I found a work around. Create a job that Executes Package. And then Execute the Job from the Stored Procedure using the code. But I dont think its the best of ways to do this. There should be a better way.
sp_start_job { [@job_name =] 'job_name' | [@job_id =] job_id } [ , [@error_flag =] error_flag] [ , [@server_name =] 'server_name'] [ , [@step_name =] 'step_name'] [ , [@output_flag =] output_flag]
I found a piece for code for Executing DTS packaages from Stored Procs. I still Have to try that but i am hopeful that it might work (I have never used DTS. U can say I am new to SQL server. I am Oracle user for 10 Years):
USE [SSIS]
GO
/****** Object: StoredProcedure [dbo].[ExecSSISPackage] Script Date: 06/15/2005 16:20:18 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE procedure [dbo].prova_run as
declare @hr1 int, @hr2 int, @hr3 int, @hr4 int,
@oPKG int
--create package
EXEC @hr1 = sp_OACreate 'DTS.Package', @oPKG OUT
--Load package
EXEC @hr2 = sp_OAMethod @oPKG, 'LoadFromSQLServer("localhost", "sa", "sa", 256, , , , "DTS1")', NULL
--execute package
EXEC @hr3 = sp_OAMethod @oPKG, 'exec'
--cleaning
EXEC @hr4 = sp_OADestroy @oPKG
GO
SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER OFF
now the only problem i see here is how to execute ssis package from procedure while sending it the order data. And returning the flag to thru the service broker to the application.
