locked
How to Stop the Pipeline Running when Store Procedure Activity Fail? RRS feed

  • Question

  • Hi,

    Could someone help to resolve the store procedure issue which I’ve been trying in last couple days but fail to have a solid solution:

    I current got 2 store procedure activity chaining up in the pipeline : SP1_Activity and SP2_Activity – the output dataset (sp1_output) from the first store procedure activity is the input dataset for the second store procedure activity.  If the first store procedure got any error, there will be no data being insert into the output dataset. When that happen I don’t want the second activity running at all. However it seems data factory doesn’t give much option for that: Even the output dataset is empty, the second activity still kick off as long as the store procedure in the 1st activity is finished, regardless of whether it throw out the error! Pipeline file as below.

    My question is: Is there a way to control the workflow – If the first activity fail, then the whole pipeline stop running? 

    Many thanks.

    Regards

    {
        "name": "FailTestActivityPipeline",
        "properties": {
            "activities": [
               
                {
                    "type": "SqlServerStoredProcedure",
                    "typeProperties": {
                        "storedProcedureName": "sp1"
                    },
                    "outputs": [
                        {
                            "name": "sp1_output"
                        }
                    ],
                    "scheduler": {
                        "frequency": "Day",
                        "interval": 1
                    },
                    "name": "SP1_Activity"
                },
                {
                    "type": "SqlServerStoredProcedure",
                    "typeProperties": {
                        "storedProcedureName": "sp2"
                    },
                    "inputs": [
                        {
                            "name": "sp1_output"
                        }
                    ],
                    "outputs": [
                        {
                            "name": "sp2_output"
                        }
                    ],
    
                    "scheduler": {
                        "frequency": "Day",
                        "interval": 1
                    },
                    "name": "SP2_Activity"
                }
                
            ],
            "start": "2017-04-11T00:00:00Z",
            "end": "2017-04-12T00:00:00Z",
            "isPaused": false,
            "pipelineMode": "Scheduled"
        }
    }




    • Edited by stephen huo Thursday, November 2, 2017 5:43 PM
    Thursday, November 2, 2017 3:58 PM

Answers

  • Make couple tries and got this solved:

    Since I’ve implemented try (Begin Try …. End Try)  in the store procedure, need to Throw the error out in the Catch statement.

    Once the activity got errors, all the activities that depend on it, either direct or indirect, will be place on hold (waiting). We could manual choose to rerun the activity in the Monitor page.

    Regards

    • Marked as answer by stephen huo Monday, January 8, 2018 10:58 AM
    Monday, January 8, 2018 10:58 AM

All replies

  • your expectation is correct, if the input-dataset is not ready because the activity that produces it failed, all dependent activities should not start at all

    are you sure that your inputs and outputs are configured correctly?

    it might help if you move the second activity to a separate pipeline - have you tried this?

    -gerhard


    Gerhard Brueckl
    blogging @ http://blog.gbrueckl.at
    working @ http://www.pmOne.com

    Friday, November 3, 2017 9:02 AM
  • Hi Gerard,

      Thanks for reply! I’ve been trying everything, including chaining activities within the same pipeline, or chaining two separate pipeline, the results are the same. What I found is for store procedure activity it simply didn’t check the output dataset at all. In my example above, even the output table from SP1_activity doesn’t exist in the SQL database, the second store procedure activity will still kick off as long as the output table from SP1_activity is being defined as the input dataset.

      I am disappointed on this – I understand that for data factory V1 there are no conditional activity that can control the success/fail for the workflow, but at least there should be a way to stop the workflow if one of the chaining activity is failed. Otherwise it doesn’t make sense to me why the store procedure activity had to have a output dataset. Unless there is sth I missed in my test?  

    Friday, November 3, 2017 12:03 PM
  • Make couple tries and got this solved:

    Since I’ve implemented try (Begin Try …. End Try)  in the store procedure, need to Throw the error out in the Catch statement.

    Once the activity got errors, all the activities that depend on it, either direct or indirect, will be place on hold (waiting). We could manual choose to rerun the activity in the Monitor page.

    Regards

    • Marked as answer by stephen huo Monday, January 8, 2018 10:58 AM
    Monday, January 8, 2018 10:58 AM