[转]SSIS: Execute Package via Stored Procedure
本文转自:http://sqlblog.de/blog/2009/09/ssis-execute-package-via-stored-procedure/
There are two options executing SSIS packages: - xp_cmdshell command (not recommended for security reasons) - sp_start_job command The main difference between both options is the execution method. The xp_cmdshell command is a synchronous call and the sp_start_job command is an asynchronous call. xp_cmdshell command (synchronous) enable xp_cmdshell mode in Surface Area Configuration Tool for SQL Server use following procedure to execute SSIS package: DECLARE @returncode int EXEC @returncode = xp_cmdshell 'dtexec /f "PackageNameWithFullPath.dtsx"' sp_start_job (asynchronous) define job in SQL Server Agent with SSIS execution step use following procedure to start SQL Server Agent job with SSIS execution step: [msdb].dbo.sp_start_job @job_name='JobName' check execution status of job, since the call is asynchronous: SELECT [server], [start_execution_date], [stop_execution_date], [run_date], [run_duration], [run_status], [message] FROM MSDB.DBO.SYSJOBS Z INNER JOIN MSDB.DBO.SYSJOBACTIVITY A ON Z.JOB_ID = A.JOB_ID INNER JOIN ( SELECT MAX(SESSION_ID) AS SESSION_ID FROM MSDB.DBO.SYSSESSIONS ) AS B ON A.SESSION_ID = B.SESSION_ID LEFT JOIN MSDB.DBO.SYSJOBHISTORY C ON A.JOB_HISTORY_ID = C.INSTANCE_ID WHERE Z.NAME = 'JobName' There is following way to make the call of sp_start_job synchronous. sp_start_job (synchronous) define job in SQL Server Agent with SSIS execution step use following procedure to start SQL Server Agent job with SSIS execution step: ALTER PROCEDURE [dbo].[AGENT_JOB_CHECK2] -- Add the parameters for the stored procedure here DECLARE @job_name nvarchar(100)='', DECLARE @maxwaitmins int = 5 AS BEGIN set NOCOUNT ON; set XACT_ABORT ON; BEGIN TRY declare @running as int declare @seccount as int declare @maxseccount as int set @maxseccount = 60*@maxwaitmins set @seccount = 0 set @running = 0 declare @job_owner sysname declare @job_id UNIQUEIDENTIFIER set @job_owner = SUSER_SNAME() -- get job id select @job_id=job_id from msdb.dbo.sysjobs sj where sj.name=@job_name -- invalid job name then exit with an error if @job_id is null RAISERROR (N'Unknown job: %s.', 16, 1, @job_name) -- output from stored procedure xp_sqlagent_enum_jobs is captured in the following table declare @xp_results TABLE ( job_id UNIQUEIDENTIFIER NOT NULL, last_run_date INT NOT NULL, last_run_time INT NOT NULL, next_run_date INT NOT NULL, next_run_time INT NOT NULL, next_run_schedule_id INT NOT NULL, requested_to_run INT NOT NULL, -- BOOL request_source INT NOT NULL, request_source_id sysname COLLATE database_default NULL, running INT NOT NULL, -- BOOL current_step INT NOT NULL, current_retry_attempt INT NOT NULL, job_state INT NOT NULL) -- start the job declare @r as int exec @r = msdb..sp_start_job @job_name -- quit if unable to start if @r<>0 RAISERROR (N'Could not start job: %s.', 16, 2, @job_name) -- start with an initial delay to allow the job to appear in the job list (maybe I am missing something ?) WAITFOR DELAY '0:0:10'; set @seccount = 10 -- check job run state insert into @xp_results execute master.dbo.xp_sqlagent_enum_jobs 1, @job_owner, @job_id set @running= (SELECT top 1 running from @xp_results) while @running<>0 and @seccount < @maxseccount begin WAITFOR DELAY '0:0:10'; set @seccount = @seccount + 10 delete from @xp_results insert into @xp_results execute master.dbo.xp_sqlagent_enum_jobs 1, @job_owner, @job_id set @running= (SELECT top 1 running from @xp_results) end -- result: query SELECT [server], [start_execution_date], [stop_execution_date], [run_date], [run_duration], [run_status], -- 0: failed, 1: success, null: running [message] FROM MSDB.DBO.SYSJOBS Z INNER JOIN MSDB.DBO.SYSJOBACTIVITY A ON Z.JOB_ID = A.JOB_ID INNER JOIN ( SELECT MAX(SESSION_ID) AS SESSION_ID FROM MSDB.DBO.SYSSESSIONS ) AS B ON A.SESSION_ID = B.SESSION_ID LEFT JOIN MSDB.DBO.SYSJOBHISTORY C ON A.JOB_HISTORY_ID = C.INSTANCE_ID WHERE Z.NAME = @job_name -- result: not ok (=1) if still running --if @running <> 0 --return 0 --else --return 1 END TRY BEGIN CATCH DECLARE @ErrorMessage NVARCHAR(4000), @ErrorNumber INT, @ErrorSeverity INT, @ErrorState INT, @ErrorLine INT, @ErrorProcedure NVARCHAR(200); SELECT @ErrorNumber = ERROR_NUMBER(), @ErrorSeverity = ERROR_SEVERITY(), @ErrorState = ERROR_STATE(), @ErrorLine = ERROR_LINE(), @ErrorProcedure = ISNULL(ERROR_PROCEDURE(), '-'); SELECT @ErrorMessage = N'Error %d, Level %d, State %d, Procedure %s, Line %d, ' + 'Message: '+ ERROR_MESSAGE(); RAISERROR ( @ErrorMessage, @ErrorSeverity, 1, @ErrorNumber, -- original error number. @ErrorSeverity, -- original error severity. @ErrorState, -- original error state. @ErrorProcedure, -- original error procedure name. @ErrorLine -- original error line number. ); END CATCH END [Source: http://blog.boxedbits.com/archives/124]
posted on 2014-03-03 15:41 freeliver54 阅读(559) 评论(0) 编辑 收藏 举报