Azure Data Factory ForEach Activity to use DataBricks Notebook Functionality

Question

I have requirement to Fech the list of values from Azure SQL database table column value to give the input to FOREACH activity and inside ForEach activity, we are calling azure databricks notebook. how do we pass the value end to end. specifically, how do we get parameter value from foreach to notebook.

below scenario I tried but facing issues.

Lookup activity(connected source dataset (azure sql db) --> ForEach activity(@activity('Lookup1').output.value) --- > notebook (how do we get parameter)

Notebook input:

{
    "notebookPath": "/Users/notebookpath/TestForach",
    "baseParameters": {
        "forachparam": {
            "SourceDetailsID": 1,
            "SystemName": "test",
            "FilePath": "test/path/",
            "LoadFrequencyType": "xxx",
            "IsActive": true,
            "LastLoadDateTime": "2023-02-20T17:21:44.117Z"
        }
    }
}

error :

Activity failed because an inner activity failed for notebook level

The value type 'System.Collections.Generic.Dictionary`2[[System.String, mscorlib, Version=4.0.0.0, Culture=neutral, PublicKeyToken=adssdasd],[System.Object, mscorlib, Version=4.0.0.0, Culture=neutral, PublicKeyToken=sdasddasd]]', in key 'forachparam' is not expected type 'System.String'

We tried below parameter read in notebook level

dbutils.widgets.text("item","")
item = dbutils.widgets.get("item")

Please advice me.

Answer 1

I got same error, when I tried in my environment.

This error occurs when you directly gave @item()(Object value) to the Notebook parameter. Currently, Notebook activity do not support passing Object. So, pass it as a string @string(item()) and convert back to JSON using json module like below.

Notebook Execution from ADF: