Azure Data Factory
An Azure service for ingesting, preparing, and transforming data at scale.
9,609 questions
ADF - Switch Case issues
![](data:image/svg+xml, %3Csvg xmlns='http://www.w3.org/2000/svg' height='64' class='font-weight-bold' style='font: 600 30.11764705882353px "SegoeUI", Arial' width='64'%3E%3Ccircle fill='hsl(265.6, 46%, 35%)' cx='32' cy='32' r='32' /%3E%3Ctext x='50%25' y='55%25' dominant-baseline='middle' text-anchor='middle' fill='%23FFF' %3ED%3C/text%3E%3C/svg%3E)
Dave
96
Reputation points
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
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:
Switch pipeline called from outer pipeline:
{
"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": []
}
}
{count} votes
-
![](data:image/svg+xml, %3Csvg xmlns='http://www.w3.org/2000/svg' height='64' class='font-weight-bold' style='font: 600 30.11764705882353px "SegoeUI", Arial' width='64'%3E%3Ccircle fill='hsl(172.8, 70%, 26%)' cx='32' cy='32' r='32' /%3E%3Ctext x='50%25' y='55%25' dominant-baseline='middle' text-anchor='middle' fill='%23FFF' %3EMM%3C/text%3E%3C/svg%3E)
MartinJaffer-MSFT 26,031 Reputation points2021-02-26T18:04:24.32+00:00 Hello anonymous user-8967 and welcome to Microsoft Q&A.
If I understand you correctly, you have a case statement set up. When you use a value passed from the same pipeline, it works fine. When you use a value passed from a parent pipeline it does not. Is this correct?
-
Dave 96 Reputation points
2021-03-01T10:18:25.867+00:00 Yes that is correct Martin.
As shown in my debug pipeline runs in the screenshot, the activity name does not appear to be updated when I call this switch pipeline from the outer pipeline. So I am wondering if the outer pipeline is calling an older version of the inner pipeline.
-
MartinJaffer-MSFT 26,031 Reputation points
2021-03-01T23:40:19.053+00:00 anonymous user-8967
Your Update 3 gives great insight, and I believe that is the root of the issue. I ran into something similar in Synapse Spark notebooks. In that case when one notebook called another notebook (like execute pipeline), it was calling the last published child notebook without unpublished changes.In my reproduction attempts, I am simplifying this down to use Set Variable or Wait activities instead of stored procedures.
In one or two of my debug runs, I am taken to the wrong child pipeline run. I'm still trying to get a reliable repro.To clarify, are you using Debug runs or Trigger now, or a combination?
Is there a specific set of steps in which you experience this? -
Dave 96 Reputation points
2021-03-04T03:35:25.193+00:00 @MartinJaffer-MSFT - Sorry for the delayed response.
I am using debug runs, not triggers, I haven't even merged the feature branch into the main branch yet (I don't think you can use triggers unless the pipeline is published).
So would this be considered a bug with ADF? Should I submit an issue on Github about this or raise an MS Support ticket?
I am going to try and clone the switch pipeline and update the reference from the parent pipeline and see if that will work as a workaround. I'll update after I try it, but I can't think of any other way to resolve this.
-
MartinJaffer-MSFT 26,031 Reputation points
2021-03-08T21:58:43.887+00:00 anonymous user-8967
MS Support ticket is the appropriate route. The Github is for documentation related issues.
Sign in to comment