为ETL进程调用/调度多个SSIS作业--什么是失败？

Question

所以我有个程序：

ALTER PROCEDURE [dbo].[use___MasterLoadAll]
AS
--Must run in order
EXEC [dbo].[use__MasterLoad.Staging];
EXEC [dbo].[use__MasterApplyBRs];
EXEC [dbo].[use__MasterLoad.pdw.*];
EXEC [dbo].[use__MasterLoad.FPDW_Cube];

我的问题是，如果控制器上的业务规则失败了，我想停止这个过程-下面是执行dbo.使用__MasterApplyBRs中的内容

ALTER PROCEDURE [dbo].[use__MasterApplyBRs]
AS
EXEC [dbo].[usp_ApplyDMBusinessRules];
EXEC [dbo].[usp_ApplyIndirectBusinessRules];

然后执行dbo.usp_ApplyDMBusinessRules

ALTER PROCEDURE [dbo].[usp_ApplyDMBusinessRules]
AS
-- Set up the exection
Declare @execution_id bigint
EXEC [SSISDB].[catalog].[create_execution] @package_name=N'DMController.dtsx', @execution_id=@execution_id OUTPUT, 
@folder_name=N'StagingImport', @project_name=N'BusRulesDirect', @use32bitruntime=False, @reference_id=Null
DECLARE @DWConnStr sql_variant = N'Data Source=DWSERVER;Initial Catalog=DataWarehouse;Provider=SQLNCLI11.1;Integrated Security=SSPI;Auto Translate=False;'
EXEC [SSISDB].[catalog].[set_execution_parameter_value] @execution_id,  @object_type=20, @parameter_name=N'ppDWConnStr', @parameter_value=@DWConnStr

-- System params
DECLARE @LOGGING_LEVEL smallint = 3
EXEC [SSISDB].[catalog].[set_execution_parameter_value] @execution_id,  @object_type=50, @parameter_name=N'LOGGING_LEVEL', @parameter_value=@LOGGING_LEVEL
DECLARE @Sync smallint = 1
EXEC [SSISDB].[catalog].[set_execution_parameter_value] @execution_id,  @object_type=50, @parameter_name=N'SYNCHRONIZED', @parameter_value=@Sync

-- Execute package
EXEC [SSISDB].[catalog].[start_execution] @execution_id

IF EXISTS (select 1 FROM ssisdb.catalog.executions where execution_id = @execution_id AND status = 4)
RAISERROR('Start with Google; End with http://dba.stackexchange.com/questions/ask', 16, 1)

然后执行dbo.usp_ApplyIndirectBusinessRules

ALTER PROCEDURE [dbo].[usp_ApplyIndirectBusinessRules]
AS

-- Set up the exection
Declare @execution_id bigint
EXEC [SSISDB].[catalog].[create_execution] @package_name=N'InDirectController.dtsx', @execution_id=@execution_id OUTPUT, 
@folder_name=N'StagingImport', @project_name=N'BusRulesIndirect', @use32bitruntime=False, @reference_id=Null
DECLARE @DWConnStr sql_variant = N'Data Source=DWSERVER;Initial Catalog=DataWarehouse;Provider=SQLNCLI11.1;Integrated Security=SSPI;Auto Translate=False;'
EXEC [SSISDB].[catalog].[set_execution_parameter_value] @execution_id,  @object_type=20, @parameter_name=N'ppDWConnStr', @parameter_value=@DWConnStr

-- System params
DECLARE @LOGGING_LEVEL smallint = 3
EXEC [SSISDB].[catalog].[set_execution_parameter_value] @execution_id,  @object_type=50, @parameter_name=N'LOGGING_LEVEL', @parameter_value=@LOGGING_LEVEL
DECLARE @Sync smallint = 1
EXEC [SSISDB].[catalog].[set_execution_parameter_value] @execution_id,  @object_type=50, @parameter_name=N'SYNCHRONIZED', @parameter_value=@Sync

-- Execute package
EXEC [SSISDB].[catalog].[start_execution] @execution_id

IF EXISTS (select 1 FROM ssisdb.catalog.executions where execution_id = @execution_id AND status = 4)
RAISERROR('Start with Google; End with http://dba.stackexchange.com/questions/ask', 16, 1)

最后，每个控制器都有类似的TSQL调用一堆业务规则包--但是如果这些包中的任何一个失败，Controller将继续并结束报告失败-

因此，如果控制器在业务规则中报告失败，我需要MasterLoadAll来停止-如何停止？

Answer 1

您考虑过使用SQL作业来运行包吗？如果发生错误时每个包都失败，则可以立即退出作业。

只是一个主意..。