存储过程未从SSIS中提供正确的结果调用 [英] Stored Procedure not giving correct result calling from SSIS

问题描述

从SSIS包调用以下存储过程。存储过程的结果通过结果集存储在变量中。

The below stored procedure is being called from SSIS Package. The result of stored procedure is stored in variable through resultset.

现在问题是它没有在结果集中显示"正在运行"。

now the problem is it is not showing 'Running' in a resultset.

我需要改变一切。请帮我解决这个问题。

Do I need to change anything. Please help me to resolve the issue.

CREATE PROC  [dbo].[MASTER_STATUS]
AS
BEGIN

SET NOCOUNT ON; 
--Declare all variables 

--Define an infinite loop
WHILE 1 = 1
BEGIN

DECLARE @execution_result varchar (100) 

IF OBJECT_ID('tempdb..#MASTER_STATUS') IS NOT NULL
DROP TABLE #MASTER_STATUS

SELECT TOP 1  e.package_name ,
e.execution_id, 
e.[executable_name] , 
es.[execution_path] , 
es.[execution_duration] , 
es.start_time,
es.end_time,
[execution_result] = CASE ex.[status]
			   WHEN 1 THEN 'created' 
               WHEN 2 THEN 'Running' 
               WHEN 3 THEN 'canceled' 
               WHEN 4 THEN 'failed' 
               WHEN 5 THEN 'pending' 
               WHEN 6 THEN 'ended unexpectedly' 
               WHEN 7 THEN 'succeeded' 
               WHEN 8 THEN 'stopping' 
               WHEN 9 THEN 'completed' 

END 
INTO #MASTER_STATUS
FROM   SSISDB.[catalog].[executables] e 
INNER JOIN SSISDB.[catalog].[executable_statistics] es ON es.[executable_id] = e.[executable_id] 
INNER JOIN SSISDB.[catalog] .[executions] ex ON e.execution_id = ex.execution_id
where e.package_name = 'DMStables.dtsx' 
and  e.[executable_name] = 'MASTER_STATUS' 
and CONVERT (DATE, es.start_time) = CONVERT(Date,GETDATE())
order by es.start_time DESC


IF @execution_result IS NULL or @execution_result = ''
	SELECT 'failed' AS execution_result
ELSE
	SELECT @execution_result AS execution_result


IF  @execution_result = 'Running'
Begin
	WAITFOR DELAY '00:00:30'
	CONTINUE;
End
Else
Begin
	BREAK;
End

SELECT  @execution_result = execution_result from #MASTER_STATUS ORDER BY start_time DESC

END
END

非常感谢技术人员

推荐答案

hi 

hi 

没有数据很难 

without data is hard 

但是您将数据插入到tbl  ＃ MASTER_STATUS

but you insert your data into tbl #MASTER_STATUS

并将结果与​​ @ execution_result

这有意义吗？

我想你应该知道 移动此行

i guess you shoud  move this line

SELECT   @ execution_result
= execution_result 来自
＃ MASTER_STATUS ORDER
BY start_time DESC

SELECT  @execution_result = execution_result from #MASTER_STATUS ORDER BY start_time DESC

这篇关于存储过程未从SSIS中提供正确的结果调用的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！