1

So I've got a procedure:

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];

My issue is if the Business Rules fail on the controller I'd like to stop the procedure - Here is what's in the EXEC [dbo].[use__MasterApplyBRs]

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

Then EXEC [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)

Then EXEC [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)

So finally each of those controllers have similar TSQL that calls a bunch of Business Rules Packages - but if anyone of those packages fails the Controller will continue and end reporting failure -

Therefore if a Controller reports failure within the Business Rules I need MasterLoadAll to stop - How?

1
  • 1
    Found a link that may help from Erland Sommarskog on error handling. Basically, the keys appear to be (1) use TRY...CATCH around everything; (2) always re-raise errors in the CATCH block; and (3) use the fact that every stored procedure returns an integer value, 0 by default. So, always return a non-zero value when a stored proc fails. See the link for the full details.
    – RDFozz
    Mar 15 '17 at 23:32
2

Have you considered using a SQL job to run the packages? If each package fails when an error occurs, you can have your job exit immediately.

Just an idea...

2
  • Nestor - Yes I have, that's the "easy" way - I'm new and trying to learn alternative approaches to similar problems. It's currently running in a SQL Job, but I'd like to understand the code. Thanks for the reply. Mar 16 '17 at 16:11
  • Got it. I think the try...catch option that RDFozz commented is probably a good place to start.
    – Nestor
    Mar 16 '17 at 17:37

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.