Get Error Message of activity failed in switch activity

Question

Get Error Message of activity failed in switch activity

Swapneel Hosur 106

I have been trying to get error message of an activity located inside the switch activity.
The way to get error message is "activity('Insert into Sales')?.Error?.message". The question mark symbol helps us to get error message if the activity is invoked or not invoked. This statement is getting executed successfully in case of if condition (for activities in True as well as False condition)

Now, with Switch activity I have three cases File Empty, File Duplicate and File New. The Switch activity only provides us with "expression" value to determine in which block it would fall. Like I am purposely trying to fail an activity(set duplicate variable) inside File Duplicate case, I won't have any information about the (set duplicate variable)activity unless I am referencing the (set duplicate variable)activity.

Techniques Tried:

1 When I try to mention activities in "Set Variable", I get error stating "The output of the activity can't be referenced since it is either not an ancestor to the current activity or does not exist". In this case ADF doesn't know in which block the failure would occur.
2. I tried inputting the entire concat function inside of a variable to invoke error expression based on expression value of switch activity, but the same is not getting evaulated at run time. Instead, it just shows me the contents of the variable with switch output.
For example: "Switch Activity Failed because an inner activity failed"|concat(....)

There is a property in SSIS called "Delay Validation". I wanted to know if similar implementation is possible with Swicth activity to delay validation of all the activities inside of the switch activity until runtime.

Thanks,
Swapneel

Answer accepted by question author

0 additional answers

Your answer

Answer 1

Hello @Swapneel Hosur ,
Thanks for the question and using MS Q&A platform.

As I understand you are having trouble capturing the error message of an activity inside switch, from an activity outside switch.

What I recommend is placing a Set Variable inside each of the switch cases to record the error, so you can refer to it outside the switch. Pipeline variables have a single 'scope', meaning no matter where in the pipeline you are, there is only one instance of the variable. Set Variables inside loops point to the same place Set Variables outside loops do.

I'm not practiced with SSIS, but from what I read, I don't think "Delay Validation" is an option in pipelines, beyond the ?..

You will get this same "The output of the activity can't be referenced since it is either not an ancestor to the current activity or does not exist" when you try to reference an activity inside ForEach, Until, If activities. I'll explain the ForEach and Until cases.

In a loop, the same activities are run multiple times. As they are the same, they have the same names. Since they have the same names, you can't specify which instance. Thus referencing inner activities is disallowed.

Now you might note, "Activities in different cases are required to have different names, and Switch only runs once. Why can't we reference these?"
I'm not sure, but I suspect it is related to that concept of "Delay Validation". When an activity is NOT run, it is given the status "Skipped". However, I noticed this does not apply to inner activities of the not-run cases. I tested by linking a Fail activity to a Wait activity by a skip-dependency, and ran the other branch.
Since this activity was not run in the inactive branch, it probably doesn't exist in any way.

The "ancestor to the current activity" means a direct lineage of activities connected by dependencies is required. This is because dependencies establish the order in which activites are executed. Without this, you might try to get the output of Lookup before it completes, or get a variable before it is set. The ability to reference an activity is linked to the dependency chain. The inner activities do not have a dependency linking to the outer.
Now you might ask "why can't we make an exception here? The inner activities belong to the switch, and I have dependency on the switch."
I don't know. I'd have to ask the developers. Maybe it is possible with a feature request. Maybe the back-end architecture precludes it. My hunch is that each inner section is run on a separate compute context.

Please do let me if you have any queries.

Thanks
Martin

Please don't forget to click on or upvote button whenever the information provided helps you. Original posters help the community find answers faster by identifying the correct answer. Here is how
Want a reminder to come back and check responses? Here is how to subscribe to a notification
- If you are interested in joining the VM program and help shape the future of Q&A: Here is how you can be part of Q&A Volunteer Moderators

Swapneel Hosur 106 Reputation points

2022-12-28T21:43:15.207+00:00

Hey Martin,

Thanks for your reply. I initially thought of having seprate "Set Variable" Activities for each of the Switch Cases, however my pipeline already has around 40 activities and as we know we cannot have more than 40 activities in one pipeline.

Another solution I thought of was using if statement in the expression as below:
if(equals(switchactivity.expression.output,'FileEmpty'),'Get error messages of activities from FileEmpty Case',if(equals(switchactivity.expression.output,'FileDuplicate','Get error messages of activities from FileDuplicate Case','Get error messages of activities from FileNew Case'))

For above logic if validation is applied on runtime then this might work, but with validation in place I am not really sure how to proceed with and I am getting "ancestor to the current activity" error.

And yes you're right, Switch Activity does not behave like other logical activity like If Condition wherein we can retrive the error messages from both the conditions. And yes the inner section is executed as an individual activity and not part of switch activity, however Switch Activity waits till completion of the case activities but the output part of switch case does not indicate/ express any infomation related to inner case activities.

I think I can touchbase with the developers and raise a ticket regarding the same. Thank you for your inputs on this.

Regards,
Swapneel
MartinJaffer-MSFT 26,161 Reputation points

2022-12-28T23:01:37.007+00:00

@Swapneel Hosur Thanks for the response.

You said Switch does NOT behave like If ? I must test that, my understanding of pipelines might be faulty!

Hearing you maxed out the activity limit, I imagine you had to cut out some stuff.
We could push the entire switch activity into a child pipeline, and with clever use of a copy activity and additional columns to write the error message to a file. The parent pipeline then uses a lookup activity to read the error message.

There is also the possibility of abusing activity failures to create logic paths in the pipeline and not use Switch activity.
For this I use a set variable activity on a boolean variable. In the set variable I do like if( a < b , return true, return "not a bool") Returning a string breaks the set variable, making a failure. Then (picture below)
Swapneel Hosur 106 Reputation points

2022-12-29T17:42:34.273+00:00

Hi Martin,

The solutions you presented are great, however pushing activities to child pipeline to avoid maxing activities is good but this might add to a lot of maintenance. We have to write error messages somewhere and then read it by lookup activity. This might also change the business logic and would impact the flow of pipeline.

For now I will restrict the error message to switch activity only, I will check with the developers/ support team on the activity's feasbility of throwing error.

IThank you for your time and efforts on this topic.

Regrads,
Swapneel

Share via

Get Error Message of activity failed in switch activity

0 additional answers

Your answer