Can SQL task pass its outputs to ForEach task?

[Fikrat]

Hi there,
If I understood correctly, Roland said output SQL task can be used as input to ForEach task in Workflows. I tried that and used the expression sqlTaskName.output.rows, but Databricks rejected that expression. Anyone know how to do that? 

[Walter_C]

Can you confirm if this are the steps being followed:

• Create the SQL Task: Ensure your SQL task is correctly set up and produces the desired output. For example:

  SELECT customer_name, market FROM example_customers;

• Reference the SQL Task Output in ForEach Task:

  • Use the correct syntax to reference the rows output. The expression should be in the format {{ tasks.sqlTaskName.output.rows }}.
  • Ensure that the SQL task name (sqlTaskName) matches the task key you have defined in your workflow.

• Configure the ForEach Task:

  • Set the inputs field of the ForEach task to {{ tasks.sqlTaskName.output.rows }}.
  • Define the nested task within the ForEach task to process each row. For example:

    {
      "task_key": "process_customers_iteration",
      "parameters": {
        "customer_name": "{{ input.customer_name }}",
        "market": "{{ input.market }}"
      },
      "sql_task": {
        "file": {
          "path": "/path/to/your/sql/file.sql"
        }
      }
    }

[Fikrat]

Hi there,

Thanks for looking at this. I followed your instructions to the letter, but Databricks refused to accept that dynamic expression for the input of foreach task, see the screenshot.And output.rows expression is not among dynamic expressions offered by 'Inputs' box.Also, I can't even save that setting for ForEach task, let alone to execute it. Please advise.

[Walter_C]

May I know from which blog or webinar did you get the information provided in the original post?

[Fikrat]

I got it from this:
What's New in Databricks Workflows - with Live Demos!
He's demoing haw sql task output can be consumed at around 9-th min

[Walter_C]

I am asking internally on this, will get back to you once I have more details