Forum Discussion

arehman36's avatar
arehman36
Regular Visitor
2 days ago

Fail Message Not Working in Fail Activity

Good day

I have a lookup activity that returns an output like this: 

{ "count": 6, "value": [ 

{

    "SchemaTablename": "Schema.TableName", 

    "QueryText": "Query goes here",

    "OnPremTableName": "[dbo].[TableName" 

},

After the Lookup, I have a ForEach Activity that contains 3 activities

  • Lookup: OnPremColumnCount: returns the column count for a table on-premise. example output: 
{ "firstRow": { "OnPremColumnCount": 30 } }
  • Lookup: CloudColumnCount: returns the column count for a table in fabric warehouse. 
{ "firstRow": { "OnPremColumnCount": 30 } }
  • IF Condition: Checks to see if both tables have the same number of columns. If False, there is a fail activity.

Now my issue is that I am trying to write an error message that tells me the number of columns on-prem, on cloud, and the on-premise and cloud table name. 

Here is what I have inside the Fail Message:

@concat(

    'Columns do not match. On-premise table column count: ', string(activity('OnPremColumnCount').output.firstRow),'.', ' ',

    'Cloud table column count: ', string(activity('CloudColumnCount').output.firstRow),'.',    string(activity('LookupGetTableList').output.OnPremTableName),

    string(activity('LookupGetTableList').output.SchemaTablename))

The Error Code is: ActivityFailed.

I want it to show an error message like

Columns do not match. On-premise table count: 20. Cloud table count: 15. [Schema].[MY_TABLE]. [Schema].[My_Table]

I keep getting this error when I run the pipeline.

I am not sure why this is happening. I suspect that I am not accessing the output of my previous activities correctly. More so I think it's an issue with accessing the output from the First LookUp Activity (LookupGetTableList).

Here is a screenshot of my entire pipeline

Your help is greatly appreciated.

Thanks,

A

data pipeline

2 Replies

  • ShivekMaharaj's avatar
    ShivekMaharaj
    Icon for Memorable Member rankMemorable Member
    1 day ago

    Hi arehman36​,

    I think there are two expression issues worth checking here.

    Your first Lookup returns multiple rows in output.value, and the ForEach is presumably iterating over that array.

    Because of that, inside the ForEach I would not use:

    activity('LookupGetTableList').output.SchemaTableName

    The current table should be taken from the current ForEach item instead:

    item().SchemaTableName

    Microsoft's Lookup activity documentation explains that multi-row Lookup results are returned under the value array, and the ForEach documentation uses item() to access the row currently being processed.

    Your two column-count Lookups also return their values under firstRow.

    So based on the output you posted, the Fail message would look conceptually like this:

    @concat(
'Columns do not match. On-premise table column count: ',
string(activity('OnPremColumnCount').output.firstRow.OnPremColumnCount),
'. Cloud table column count: ',
string(activity('CloudColumnCount').output.firstRow.OnPremColumnCount),
'. Schema/Table: ',
string(item().SchemaTableName)
)

    There is one detail to check carefully in your CloudColumnCount output.

    In the screenshot you posted, CloudColumnCount appears to return:

    firstRow -> OnPremColumnCount

    If that is the actual output property name, then the expression must use OnPremColumnCount exactly as shown above.

    If your cloud query is intended to return a property called CloudColumnCount, I would instead alias the result in that query as CloudColumnCount and then reference:

    activity('CloudColumnCount').output.firstRow.CloudColumnCount

    I would also keep the Fail activity Error Code as a simple literal while testing, for example:

    ColumnCountMismatch

    Microsoft's Fail activity documentation requires both the error message and error code to resolve to strings.

    The ErrorCodeNotString error therefore points to the Fail activity being unable to interpret one of those values correctly, rather than the Fail activity itself not working.

    I would test the three dynamic values separately first:

    string(activity('OnPremColumnCount').output.firstRow.OnPremColumnCount)

string(activity('CloudColumnCount').output.firstRow.OnPremColumnCount)

string(item().SchemaTableName)

    Once each one resolves successfully on its own, combine them with concat.

    The main correction I would make first is replacing the reference to LookupGetTableList.output.SchemaTableName with item().SchemaTableName because you are already inside the ForEach iteration.

    AI-assisted drafting: AI was used to help structure and phrase this response. I reviewed and validated the technical content before posting.

  • v-sathmakuri's avatar
    v-sathmakuri
    Icon for Community Support rankCommunity Support
    1 hour ago

    Hi arehman36​ ,

    Could you review the suggestion provided above and let us know if you have any additional questions, we are happy to address. 

    Thanks!!