ADF - Switch Case issues

Dave 96 Reputation points
2021-02-24T23:10:03.02+00:00

I'm having an issue with a switch statement where some of the cases not working. It seems to be picky with what text I use for the case labels it seems like.

I have a case value 'Copy Table Direct Oracle Run Procedure' (line 295 below) that is supposed to call an inner pipeline if hit. When I do a debug run I get the following output from the switch:
"expression": "Copy Table Direct Oracle Run Procedure"

It looks correct, but for some reason it is not going into the case, but instead hitting my default case, which I use to handle unrecognized cases and it captures the error message in a variable with a custom message

"value": "BatchTypeADF value 'Copy Table Direct Oracle Run Procedure' was not recognized. Check for typos or add a case for this inside the Master switch statement"

update: Okay so if I pass into the Switch pipeline parameter ParameterJson a value of { "BatchTypeADF" : "Copy Table Direct Oracle Run Procedure" }, the correct case will execute properly. My issue is I call this Switch pipeline from a parent pipeline and pass the value of ParameterJson from there, but everything input wise still looks correct.

Is there some kind of restrictions on what you can use for your case labels? I also notice I cannot edit the case labels in the UI, they are grayed out with the text 'Default'. I've tried restarting my browser and clearing cache. I have to go into the json to edit these for some reason

update2: I'm wondering if the grayed out 'Default' text for this is actually causing it to hit the default case even when it hits the "Copy Table Direct Oracle Run Procedure" case

71803-image.png

71802-image.png

update3: What seems to be happening I think, is the outer pipeline is calling an older version of the inner pipeline. I changed an execute stored procedure activity name in the Switch pipeline from 'Throw Error Message 2' to 'Throw Error Message xxx'. When I call the switch pipeline by itself, the case runs correctly with the new activity name appears. When I call my parent pipeline, which then calls the switch pipeline, it is still using the old activity name 'Throw Error Message 2'.

What would be causing this? I did a 'Save All' and I'm running the pipeline in Debug mode using Github integration on a feature branch.

Switch pipeline called by itself:

71806-image.png

Switch pipeline called from outer pipeline:

71826-image.png

{  
    "name": "Master_Switch",  
    "properties": {  
        "description": "Contains the switch statement to call inner Batch pipelines",  
        "activities": [  
            {  
                "name": "Batch Type",  
                "type": "Switch",  
                "dependsOn": [],  
                "userProperties": [],  
                "typeProperties": {  
                    "on": {  
                        "value": "@pipeline().parameters.ParametersJson.BatchTypeADF",  
                        "type": "Expression"  
                    },  
                    "cases": [  
                        {  
                            "value": "Copy Tables Run Procedures",  
                            "activities": [  
                                {  
                                    "name": "Execute Copy Tables Run Procedures",  
                                    "type": "ExecutePipeline",  
                                    "dependsOn": [],  
                                    "userProperties": [],  
                                    "typeProperties": {  
                                        "pipeline": {  
                                            "referenceName": "CopyTables_RunProcedures",  
                                            "type": "PipelineReference"  
                                        },  
                                        "waitOnCompletion": true,  
                                        "parameters": {  
                                            "ParametersJson": {  
                                                "value": "@pipeline().parameters.ParametersJson",  
                                                "type": "Expression"  
                                            }  
                                        }  
                                    }  
                                }  
                            ]  
                        },  
                        {  
                            "value": "Copy Tables - Direct",  
                            "activities": [  
                                {  
                                    "name": "Execute Copy Tables Direct",  
                                    "type": "ExecutePipeline",  
                                    "dependsOn": [],  
                                    "userProperties": [],  
                                    "typeProperties": {  
                                        "pipeline": {  
                                            "referenceName": "CopyTables_Direct",  
                                            "type": "PipelineReference"  
                                        },  
                                        "waitOnCompletion": true,  
                                        "parameters": {  
                                            "ParametersJson": {  
                                                "value": "@pipeline().parameters.ParametersJson",  
                                                "type": "Expression"  
                                            }  
                                        }  
                                    }  
                                }  
                            ]  
                        },  
                        {  
                            "value": "Copy Tables - Query",  
                            "activities": [  
                                {  
                                    "name": "Execute Copy Tables Query",  
                                    "type": "ExecutePipeline",  
                                    "dependsOn": [],  
                                    "userProperties": [],  
                                    "typeProperties": {  
                                        "pipeline": {  
                                            "referenceName": "CopyTables_Query",  
                                            "type": "PipelineReference"  
                                        },  
                                        "waitOnCompletion": true,  
                                        "parameters": {  
                                            "ParametersJson": {  
                                                "value": "@pipeline().parameters.ParametersJson",  
                                                "type": "Expression"  
                                            }  
                                        }  
                                    }  
                                },  
                                {  
                                    "name": "Append Error Message Copy Tables Query",  
                                    "type": "AppendVariable",  
                                    "dependsOn": [  
                                        {  
                                            "activity": "Execute Copy Tables Query",  
                                            "dependencyConditions": [  
                                                "Failed"  
                                            ]  
                                        }  
                                    ],  
                                    "userProperties": [],  
                                    "typeProperties": {  
                                        "variableName": "ErrorMessageArray",  
                                        "value": {  
                                            "value": "@activity('Execute Copy Tables Query').error",  
                                            "type": "Expression"  
                                        }  
                                    }  
                                }  
                            ]  
                        },  
                        {  
                            "value": "Run Procedures - Parameters None",  
                            "activities": [  
                                {  
                                    "name": "Execute Run Procedures",  
                                    "type": "ExecutePipeline",  
                                    "dependsOn": [],  
                                    "userProperties": [],  
                                    "typeProperties": {  
                                        "pipeline": {  
                                            "referenceName": "RunProcedures_ParametersNone",  
                                            "type": "PipelineReference"  
                                        },  
                                        "waitOnCompletion": true,  
                                        "parameters": {  
                                            "ParametersJson": {  
                                                "value": "@pipeline().parameters.ParametersJson",  
                                                "type": "Expression"  
                                            }  
                                        }  
                                    }  
                                }  
                            ]  
                        },  
                        {  
                            "value": "Run Procedures - No Output",  
                            "activities": [  
                                {  
                                    "name": "Run Procedures",  
                                    "type": "ExecutePipeline",  
                                    "dependsOn": [],  
                                    "userProperties": [],  
                                    "typeProperties": {  
                                        "pipeline": {  
                                            "referenceName": "RunProcedures_NoOutput",  
                                            "type": "PipelineReference"  
                                        },  
                                        "waitOnCompletion": true,  
                                        "parameters": {  
                                            "ParametersJson": {  
                                                "value": "@pipeline().parameters.ParametersJson",  
                                                "type": "Expression"  
                                            }  
                                        }  
                                    }  
                                }  
                            ]  
                        },  
                        {  
                            "value": "Execute Raw SQL",  
                            "activities": [  
                                {  
                                    "name": "Execute Raw SQL",  
                                    "description": "",  
                                    "type": "SqlServerStoredProcedure",  
                                    "dependsOn": [],  
                                    "policy": {  
                                        "timeout": "0.07:00:00",  
                                        "retry": 0,  
                                        "retryIntervalInSeconds": 30,  
                                        "secureOutput": false,  
                                        "secureInput": false  
                                    },  
                                    "userProperties": [],  
                                    "typeProperties": {  
                                        "storedProcedureName": "sp_executesql",  
                                        "storedProcedureParameters": {  
                                            "stmt": {  
                                                "value": {  
                                                    "value": "@{pipeline().parameters.ParametersJson.RawSqlText}",  
                                                    "type": "Expression"  
                                                },  
                                                "type": "String"  
                                            }  
                                        }  
                                    },  
                                    "linkedServiceName": {  
                                        "referenceName": "SqlServer_Azure_Dynamic_MI",  
                                        "type": "LinkedServiceReference",  
                                        "parameters": {  
                                            "ConnectionKey": {  
                                                "value": "@pipeline().parameters.ParametersJson.ConnectionKey",  
                                                "type": "Expression"  
                                            }  
                                        }  
                                    }  
                                }  
                            ]  
                        },  
                        {  
                            "value": "Lookup Into Copy Table Query",  
                            "activities": [  
                                {  
                                    "name": "Execute Date Lookup into Copy Table Query",  
                                    "type": "ExecutePipeline",  
                                    "dependsOn": [],  
                                    "userProperties": [],  
                                    "typeProperties": {  
                                        "pipeline": {  
                                            "referenceName": "Lookup_CopyTableQuery",  
                                            "type": "PipelineReference"  
                                        },  
                                        "waitOnCompletion": true,  
                                        "parameters": {  
                                            "ParametersJson": {  
                                                "value": "@pipeline().parameters.ParametersJson",  
                                                "type": "Expression"  
                                            },  
                                            "TestMode": {  
                                                "value": "@pipeline().parameters.TestMode",  
                                                "type": "Expression"  
                                            }  
                                        }  
                                    }  
                                },  
                                {  
                                    "name": "Append Error Message Lookup Copy Tables",  
                                    "type": "AppendVariable",  
                                    "dependsOn": [  
                                        {  
                                            "activity": "Execute Date Lookup into Copy Table Query",  
                                            "dependencyConditions": [  
                                                "Failed"  
                                            ]  
                                        }  
                                    ],  
                                    "userProperties": [],  
                                    "typeProperties": {  
                                        "variableName": "ErrorMessageArray",  
                                        "value": {  
                                            "value": "@activity('Execute Date Lookup into Copy Table Query').error",  
                                            "type": "Expression"  
                                        }  
                                    }  
                                }  
                            ]  
                        },  
                        {  
                            "value": "Copy Tables - Oracle - Query",  
                            "activities": [  
                                {  
                                    "name": "Execute Copy Tables Query Oracle",  
                                    "type": "ExecutePipeline",  
                                    "dependsOn": [],  
                                    "userProperties": [],  
                                    "typeProperties": {  
                                        "pipeline": {  
                                            "referenceName": "CopyTables_Query_Oracle",  
                                            "type": "PipelineReference"  
                                        },  
                                        "waitOnCompletion": true,  
                                        "parameters": {  
                                            "ParametersJson": {  
                                                "value": "@pipeline().parameters.ParametersJson",  
                                                "type": "Expression"  
                                            },  
                                            "TestMode": {  
                                                "value": "@pipeline().parameters.TestMode",  
                                                "type": "Expression"  
                                            }  
                                        }  
                                    }  
                                },  
                                {  
                                    "name": "Append Error Message Copy Tables Oracle Query_copy1",  
                                    "type": "AppendVariable",  
                                    "dependsOn": [  
                                        {  
                                            "activity": "Execute Copy Tables Query Oracle",  
                                            "dependencyConditions": [  
                                                "Failed"  
                                            ]  
                                        }  
                                    ],  
                                    "userProperties": [],  
                                    "typeProperties": {  
                                        "variableName": "ErrorMessageArray",  
                                        "value": {  
                                            "value": "@activity('Execute Copy Tables Query Oracle').error",  
                                            "type": "Expression"  
                                        }  
                                    }  
                                }  
                            ]  
                        },  
                        {  
                            "value": "Copy Table Direct Oracle Run Procedure",  
                            "activities": [  
                                {  
                                    "name": "Copy Table Direct - Run Procedure Oracle",  
                                    "type": "ExecutePipeline",  
                                    "dependsOn": [],  
                                    "userProperties": [],  
                                    "typeProperties": {  
                                        "pipeline": {  
                                            "referenceName": "CopyTables_RunProcedures_Oracle",  
                                            "type": "PipelineReference"  
                                        },  
                                        "waitOnCompletion": true,  
                                        "parameters": {  
                                            "ParametersJson": {  
                                                "value": "@pipeline().parameters.ParametersJson",  
                                                "type": "Expression"  
                                            },  
                                            "TestMode": {  
                                                "value": "@pipeline().parameters.TestMode",  
                                                "type": "Expression"  
                                            }  
                                        }  
                                    }  
                                },  
                                {  
                                    "name": "Error Message - Copy Table Run SP Oracle",  
                                    "type": "AppendVariable",  
                                    "dependsOn": [  
                                        {  
                                            "activity": "Copy Table Direct - Run Procedure Oracle",  
                                            "dependencyConditions": [  
                                                "Failed"  
                                            ]  
                                        }  
                                    ],  
                                    "userProperties": [],  
                                    "typeProperties": {  
                                        "variableName": "ErrorMessageArray",  
                                        "value": {  
                                            "value": "@activity('Copy Table Direct - Run Procedure Oracle').error",  
                                            "type": "Expression"  
                                        }  
                                    }  
                                }  
                            ]  
                        }  
                    ],  
                    "defaultActivities": [  
                        {  
                            "name": "Append Error Message Default Case",  
                            "type": "AppendVariable",  
                            "dependsOn": [],  
                            "userProperties": [],  
                            "typeProperties": {  
                                "variableName": "ErrorMessageArray",  
                                "value": {  
                                    "value": "BatchTypeADF value @{replace(pipeline().parameters.ParametersJson.BatchTypeADF,'''','')} was not recognized.  Check for typos or add a case for this inside the Master switch statement",  
                                    "type": "Expression"  
                                }  
                            }  
                        }  
                    ]  
                }  
            },  
            {  
                "name": "If Any Errors Throw Error Message",  
                "type": "IfCondition",  
                "dependsOn": [  
                    {  
                        "activity": "Batch Type",  
                        "dependencyConditions": [  
                            "Completed"  
                        ]  
                    }  
                ],  
                "userProperties": [],  
                "typeProperties": {  
                    "expression": {  
                        "value": "@greater(length(variables('ErrorMessageArray')),0)",  
                        "type": "Expression"  
                    },  
                    "ifTrueActivities": [  
                        {  
                            "name": "Throw Error Message 2",  
                            "description": "Re-throws any error message from any inner pipelines that were called up to the Master pipeline",  
                            "type": "SqlServerStoredProcedure",  
                            "dependsOn": [],  
                            "policy": {  
                                "timeout": "0.07:00:00",  
                                "retry": 0,  
                                "retryIntervalInSeconds": 30,  
                                "secureOutput": false,  
                                "secureInput": false  
                            },  
                            "userProperties": [],  
                            "typeProperties": {  
                                "storedProcedureName": "sp_executesql",  
                                "storedProcedureParameters": {  
                                    "stmt": {  
                                        "value": {  
                                            "value": "RAISERROR('Master_Switch - Error from inner pipeline:  @{replace(variables('ErrorMessageArray')[0],'''','')}',15,1)",  
                                            "type": "Expression"  
                                        },  
                                        "type": "String"  
                                    }  
                                }  
                            },  
                            "linkedServiceName": {  
                                "referenceName": "SqlServer_Azure_Dynamic_MI",  
                                "type": "LinkedServiceReference",  
                                "parameters": {  
                                    "ConnectionKey": "xxxxx"  
                                }  
                            }  
                        }  
                    ]  
                }  
            }  
        ],  
        "concurrency": 6,  
        "parameters": {  
            "ParametersJson": {  
                "type": "object"  
            },  
            "TestMode": {  
                "type": "bool"  
            }  
        },  
        "variables": {  
            "ErrorMessageArray": {  
                "type": "Array"  
            }  
        },  
        "folder": {  
            "name": "Dynamic"  
        },  
        "annotations": []  
    }  
}  
Azure Data Factory
Azure Data Factory
An Azure service for ingesting, preparing, and transforming data at scale.
9,880 questions
{count} votes